Solved

Conditional Column Sums in SQL Server

Posted on 2012-09-21
Medium Priority
514 Views
... 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
Question by:origfla
• 3
• 3

LVL 5

Expert Comment

ID: 38422691
You can use the PIVOT. giving you the query in a while
0

LVL 5

Accepted Solution

sameer_goyal earned 2000 total points
ID: 38422744
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

LVL 4

Author Comment

ID: 38422929
This is awesome!!!
... 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!!

0

LVL 4

Author Comment

ID: 38422938
... i'm an idiot - I now understand!!!

thank you SO MUCH!!!!
0

LVL 4

Author Closing Comment

ID: 38422941
Awesome, FAST solution!!!
0

LVL 5

Expert Comment

ID: 38422957
You are welcome.

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

Happy coding..
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses
Course of the Month16 days, 21 hours left to enroll