RCLINE RCLINE - 1 year ago 124
SQL Question

SUM Values Treat as POS or NEG based on Text value from another column

I'm using SQL Server Managment Studio. I'm trying to SUM "Amount" column using CASE condition that if my DCIP column value = D then Treat "Amount" as Positive Integer ELSE if DCIP value = C then Treat "Amount" as a Negative Integer. End as Net Amount

DCIP Amount Name
-----------------------
C 100 Jack
C 100 Jack
D 300 Freddie
D 50 Jack
D 25 Freddie
C 100 Freddie


I'm grouping by Name, which should return the below results:

Net Amount Name
---------------
-150 Jack
225. Freddie

Answer Source

Use conditional aggregation

SELECT NAME,
       Net_Amount = Sum(CASE DCIP WHEN 'D' THEN Amount ELSE -Amount END) 
FROM   table
GROUP  BY NAME 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download