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

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 spel开发者_C百科l 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.

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

Rewrite your SQL with columns such as this:

sum(case when answer=0 then values else 0 end) negative

Ephismen,

The example you give has a hidden problem in it. Will you always have at least one positive and one negative? What do you want to happen if you only have one or the other and not both. Joining the table to itself will not work when you have multiple rows for each id and name.

A UNION of two separate queries will be able to answer these questions, but not sure if it applies for SQL server 2008.

You may be able to achieve this by using the group by clause for SQL as follows:

select id
     , name
     , sum (neg_values)    as negative
     , sum (pos_values)    as positive
  from                                    -- temporary table
     ( select id
            , name
            , sum (values) as neg_values
            , 0            as pos_values  -- placeholder
         from mytable
        where answer  = 0                 -- negative
        group by id
               , name
        union all
       select id
            , name
            , 0            as neg_values  -- placeholder
            , sum (values) as pos_values
         from mytable
        where answer  = 1                 -- positive
        group by id
               , name
      )
  group by id
         , name

The temporary table (inner select with union) will return rows similar to this:

id  name neg_value pos_value
25  tom       3000         0
25  tom          0       800
27  jerry     2100         0
27  jerry        0        80

The final select will return your desired results (summing them together).