Group by, Sum, Case problem (I think)

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

Group by, Sum, Case problem (I think)

Author

Topic

kafkas
Starting Member

2 Posts

Posted - 2010-04-07 : 10:45:53

Hi all

I have a table

ID-----TOTAL-----DIV
________________________
1----- 10----- ST1
1----- 10----- ST2
1----- 10----- ST2
1----- 10----- ST3
1----- 10----- AT1
2----- 10----- ST1
2----- 10----- ST1
2----- 10----- ST1

and i want this.

ID----- ST----- AT
_________________________________________________
1----- 40----- 10
2----- 30----- 0

I try

group by, sum,left and case when. but i can't figure it out.

Could you please help?

Any help would be appreciated.

Thanks in advance.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-07 : 11:03:49

[code]select
ID,
sum(case when left(Div,2)='ST' then TOTAL else 0 end) as ST,
sum(case when left(Div,2)='AT' then TOTAL else 0 end) as AT
from

-- making testdata in a derived table called dt
-- instead of this place your_table here
(
select 1 as ID, 10 as Total, 'ST1' as Div union all
select 1,10,'ST2' union all
select 1,10,'ST2' union all
select 1,10,'ST3' union all
select 1,10,'AT1' union all
select 2,10,'ST1' union all
select 2,10,'ST1' union all
select 2,10,'ST1'
)dt

group by ID[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.