How to sum up a field depending on an other field value?

Question

I am developing a small stored procedure on SQL server 2008. I have little knowledge on SQL queries, but enough to achieve simple tasks. However I came up with a problem I can't solve myself. Before I start explaining my problem, please pardon me if I spell a SQL query-word wrong because I am not a native English speaker.

I have 4 fields(CSV representation):

ID, NAME, VALUES, ANSWER
25, Tom , 2400 , 0                
25, Tom , 600 , 0                
25, Tom , 500 , 1                
25, Tom , 300 , 1                
27, Jerry, 100, 0                
27, Jerry, 20, 1                
27, Jerry, 60, 1                
27, Jerry, 2000, 0     

What I want to do is group by the selection by its ID and NAME, Sum up it's values in a field named positive when ANSWER = 1 and negative when ANSWER = 0.

ID, NAME, SUM, NEGATIVE, POSITIVE
25, Tom, 3000, 800                   
27, Jerry, 2100, 80

I Guess my question has been asked several times, but I wasn't able to find anything about it, probably because I am using the wrong terms. Anyway if someone could help that would save me a lot of time.

Answer 1

You'll do so with a CASE statement.

select Id
        , Name
        , SUM(case when answer = 1 then Values else 0 end) as Positive
        , SUM(case when answer = 0 then Values else 0 end) as Negative
    from MyTable
    group by Id
        , Name