CODEWITHSUNDEEP
sql-server
user1773949
user1773949

Reputation: 119

Sum Case When query

This is a general SQL question

I have this table (simplified), credits are positive, debits are negative:

Account Amount  Type
--------------------
john    25      credit  
john    45      debit  
john    5       debit  
john    15      credit  
mike    15      credit

I want to have a result that looks like this, using a single query using Case When:

Account  Total
--------------------
john     -10      
mike     15

Thanks in advance for any suggestions.

Upvotes: 1

Views: 194

Answers (3)

neelsg
neelsg

Reputation: 4842

You can also try the following if it makes more sense to you:

SELECT a.Account, b.Credits - a.Debits AS Total FROM
    (SELECT Account, SUM(Amount) AS Debits
        FROM YourTable GROUP BY Account HAVING Type = 'debit'
    ) AS a
    INNER JOIN
    (SELECT Account, SUM(Amount) AS Credits
        FROM YourTable GROUP BY Account HAVING Type = 'credit'
    ) AS b

Upvotes: 1

Joe
Joe

Reputation: 6827

sum(amount * case when type = 'debit' then -1 else 1 end)

Upvotes: 0

Mureinik
Mureinik

Reputation: 311088

You can use the CASE construct to distinguish between debit and credit entries.

SELECT   account, SUM (amount * CASE type WHEN 'credit' THEN 1 ELSE -1 END)
FROM     my_table
GROUP BY account

Upvotes: 3

Related Questions