Trying to create fields based on a case statement
- by dido
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
