Trying to create fields based on a case statement

Posted by dido on Stack Overflow See other posts from Stack Overflow or by dido
Published on 2012-04-06T16:32:26Z Indexed on 2012/04/06 17:29 UTC
Read the original article Hit count: 192

Filed under:
|
|
|
|

I'm having some trouble with the query below. I am trying to determine if the "category" field is A, B or C and then creating a field based on the category. That field would sum up payments field. But I'm running into error saying "incorrect syntax near keyword As". I am creating this in a SQL View. Using SQL Server 2008

SELECT r.id, r.category
CASE 
WHEN r.category = 'A' then SUM(r.payment) As A_payments 
WHEN r.category = 'B' then SUM(r.payment) As B_payments
WHEN r.category = 'C' then SUM(r.payment) As C_payments
END
FROM r_invoiceTable As r
GROUP BY r.id, r.category

I have data where all of the above cases should be executed because the data that I have has A,B and C

Sample Data- r_invoiceTable

Id --- Category ---- Payment
222      A      ----   50
444      A      ----   30
111      B      ----   90
777      C      ----   20
555      C      ----   40

Desired Output A_payments = 80, B_payments = 90, C_payments = 60

© Stack Overflow or respective owner

Related posts about sql

Related posts about database