?
Solved

Conditional Column Sums in SQL Server

Posted on 2012-09-21
6
Medium Priority
?
514 Views
Last Modified: 2012-09-21
... 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
Comment
Question by:origfla
  • 3
  • 3
6 Comments
 
LVL 5

Expert Comment

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

Accepted Solution

by:
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

by:origfla
ID: 38422929
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Author Comment

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

thank you SO MUCH!!!!
0
 
LVL 4

Author Closing Comment

by:origfla
ID: 38422941
Awesome, FAST solution!!!
0
 
LVL 5

Expert Comment

by:sameer_goyal
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question