• 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

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!!!
0
origfla
Asked:
origfla
  • 3
  • 3
1 Solution
 
sameer_goyalCommented:
You can use the PIVOT. giving you the query in a while
0
 
sameer_goyalCommented:
Here you go

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


Just replace 'Test' with your table name
0
 
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!
0
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!!!!
0
 
origflaAuthor Commented:
Awesome, FAST solution!!!
0
 
sameer_goyalCommented:
You are welcome.

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

Happy coding..
0
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