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!!!
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!!!
You can use the PIVOT. giving you the query in a while
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This is awesome!!!
... Could you elaborate on the
... 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!
... Could you elaborate on the
FOR Condition IN (A, B, C))
?... 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!
ASKER
... i'm an idiot - I now understand!!!
thank you SO MUCH!!!!
thank you SO MUCH!!!!
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..
MS Sql Server 2005 and above come with some really neat ways to make life easier :)
Happy coding..