Link to home
Start Free TrialLog in
Avatar of origfla
origfla

asked on

Conditional Column Sums in SQL Server

... I'm sure one of you experts could help me with this in a second

Suppose i had the following table

country | condition| Amount
US | a | 5
US | a | 6
US | b | 1
US | b | 3
US | c | 1
CA | a | 1
CA | a | 2
CA | b | 10


I'm trying to get an output that looks like this
Country | A | B | C
US | 11 | 4 | 1
CA | 3 | 10 | 0

In other words, I'm looking to do something along the lines of

Select
 country,
 SUM(Amount where country matches and condition = 'a') AS A,
 SUM(Amount where country matches and condition = 'b') AS B,
 SUM(Amount where country matches and condition = 'c') AS C
From
 mytable
??Group by country

What is the syntax to do that??

Thanks!!!
Avatar of sameer_goyal
sameer_goyal
Flag of India image

You can use the PIVOT. giving you the query in a while
ASKER CERTIFIED SOLUTION
Avatar of sameer_goyal
sameer_goyal
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of origfla
origfla

ASKER

This is awesome!!!
... Could you elaborate on the
FOR Condition IN (A, B, C)) 

Open in new window

?

... i'm also going to look online for this now...

I LOVE when i've never even heard of a function like "pivot" before!!

Thanks for your fast answer!
Avatar of origfla

ASKER

... i'm an idiot - I now understand!!!

thank you SO MUCH!!!!
Avatar of origfla

ASKER

Awesome, FAST solution!!!
You are welcome.

MS Sql Server 2005 and above come with some really neat ways to make life easier :)

Happy coding..