• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 520
  • Last Modified:

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

 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
??Group by country

What is the syntax to do that??

  • 3
  • 3
1 Solution
You can use the PIVOT. giving you the query in a while
Here you go

SELECT Country, A, B, C
SELECT Country, Condition, Amount
FROM Test) up
PIVOT (SUM(Amount) FOR Condition IN (A, B, C)) AS pvt
ORDER BY Country

Just replace 'Test' with your table name
origflaAuthor Commented:
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!
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

origflaAuthor Commented:
... i'm an idiot - I now understand!!!

thank you SO MUCH!!!!
origflaAuthor Commented:
Awesome, FAST solution!!!
You are welcome.

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

Happy coding..
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now