Multiple Group by sum - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Multiple Group by sum

Author

Topic

golyath
Starting Member

21 Posts

Posted - 2009-07-13 : 05:38:36

Hi,

I have a table with 4 columns:

Name, Type, Status and Total.

I need to do a query which produces 4 columns:

Name, Type, Total for status = 1, Total for status = 2

Currently I am grouping by Name and type to give an output which has the totals for each name/type combination which is great but I now need to get the 2 seperate total columns for each value of 'status'

Any help would be greatly appreciated.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-13 : 05:47:13

[code]
SELECT Name, Type, Status, SUM(Total)
FROM TABLE
GROUP BY Name, Type, Status[/code]
Like this??

Hope can help...but advise to wait pros with confirmation...

golyath
Starting Member

21 Posts

Posted - 2009-07-13 : 05:50:06

Thanks Waterduck,

but that would give the result:

Name, Type, Status, Sum(Total)

but what I need is:

Name, Type, And sum(total) where status = 1, And sum(total) where status = 2

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-13 : 06:22:25

SELECT Name, Type,
SUM(case when Status=1 then Total else 0 end) as status1_sum,
SUM(case when Status=2 then Total else 0 end) as status2_sum
FROM TABLE
GROUP BY Name, Type

Madhivanan

Failing to plan is Planning to fail

golyath
Starting Member

21 Posts

Posted - 2009-07-13 : 06:29:56

Thanks madhivanan,

I just had this and was about to reply when I found yours.

Brilliant, thanks!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-13 : 06:44:35