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

Question

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

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

Use conditional aggregation

SELECT NAME,
       Net_Amount = Sum(CASE DCIP WHEN 'D' THEN Amount ELSE -Amount END) 
FROM   table
GROUP  BY NAME

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

Sign up