Query 1 table - 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

Query 1 table

Author

Topic

Stan1978
Starting Member

31 Posts

Posted - 2011-07-13 : 04:42:07

Thank you for taking the time to read this question.

I have one table in a poor (my opinion) data structure.

Accid ----- Trans ------ Cash ---- Bonus
1 -------- Deposit ----- 10 ---- 0
1 -------- Deposit ----- 50---- 0
1 -------- Sale ------- -5 ---- 0
1 -------- Sale -------- 0 ---- -8
1 -------- Withdrawal - -25 ---- 0
2 -------- Sale -------- -10 ---- 0
2 -------- Sale ------- -10---- 0

I need to aggregate on account id, and sum the amounts for each specific Trans type?

Accid - Sale Cash - Sale Bonus - Deposit - Withdrawal
1, -5, -8, 60, -25
2, -20, 0, 0 , 0

Thanks in advance

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-13 : 04:51:46

This?

select
Accid,
sum(case when Trans='Cash' then Cash else 0 end) as [Sale Cash],
sum(case when Trans='Bonus' then Bonus else 0 end) as [Sale Bonus],
sum(case when Trans='Deposit' then Cash else 0 end) as [Deposit],
sum(case when Trans='Withdrawal' then Cash else 0 end) as [Withdrawal]
from table
group by Accid

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

Stan1978
Starting Member

31 Posts

Posted - 2011-07-13 : 04:55:20

Legend thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources