Select into Select - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select into Select

Author

Topic

csmoniz
Starting Member

5 Posts

Posted - 2010-07-15 : 20:23:16

Dear Friends.

I have the following situation:

Table with the following rows:
state qty
Cancelled 1
Approved 1
Finished 1
Implemented 1
Approved 2
Finished 3

I tried to do a query that return me the following results:

1) total for state (sum of states)
2) total (sum of all states)
3) Percentage: Total state / total approved (3) = x percent.

How to do that with a single query?

Thanks

karepa
Starting Member

3 Posts

Posted - 2010-07-15 : 21:51:26

Hi.

You need a procedure to do that.

Cheers,
Karepa

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-15 : 22:59:29

is this what you want ? If not post your expected result


declare @sample table
(
	state	varchar(10),
	qty	int
)

insert into @sample
select	'Cancelled',	1	union all
select	'Approved',	1	union all
select	'Finished',	1	union all
select	'Implemented',	1	union all
select	'Approved',	2	union all
select	'Finished',	3

select	state,
	[total for state]	= sum(qty),
	[total]			= sum(sum(qty)) over(),
	[percentage]		= sum(qty) * 100.0
				/ sum(sum(case when state = 'Approved' then qty else 0 end)) over ()
from	@sample
group by state

/*
state      total for state total       percentage                   
---------- --------------- ----------- ---------------------------- 
Approved   3               9           100.000000000000
Cancelled  1               9           33.333333333333
Finished   4               9           133.333333333333
Implemente 1               9           33.333333333333

(4 row(s) affected)
*/

KH
[spoiler]Time is always against us[/spoiler]

csmoniz
Starting Member

5 Posts

Posted - 2010-07-17 : 09:47:57

khtan

Thats It!!!!!
Thanks a lot. I use a Firebird Database and ´l ajust it for them.
Tks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources