Conditional Column Sums in SQL Server

Posted on 2012-09-21
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

 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??

Question by:origfla
    LVL 5

    Expert Comment

    You can use the PIVOT. giving you the query in a while
    LVL 5

    Accepted Solution

    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

    Just replace 'Test' with your table name
    LVL 4

    Author Comment

    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!
    LVL 4

    Author Comment

    ... i'm an idiot - I now understand!!!

    thank you SO MUCH!!!!
    LVL 4

    Author Closing Comment

    Awesome, FAST solution!!!
    LVL 5

    Expert Comment

    You are welcome.

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

    Happy coding..

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

    733 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now