How to write SQL query to sum deposits and withdrawals per user

Question

I have table like below with list of deposits and withdraws.i need to write a query to obtain what all are the deposits and withdrawals per user

Sample Input table

and the output should be

The sql code so for i have tried is

select name, 
       sum (case when  money >= 0 then money else 0 end) as sum_of_deposits,
       sum (case when money < 0 then  money else 0 end) as sum_of_withdrawals 
from transfers GROUP BY name

Answer 1

If you are using PostgreSQL 9.4 or + you can use the FILTER clause for aggregate function, is a new features from this version, combine with group by clause for user grouping, for example:

CREATE TABLE so (u text, v int);

INSERT INTO so VALUES ('a',-10000), ('b',10000),('a',2000),('c',-100);

SELECT u, 
sum(v) FILTER (WHERE v>=0) dep,
sum(abs(v)) FILTER (WHERE v<0)withdraws
FROM so
GROUP BY 1;

try to adjust this example for your case, the abs function return the absolute value of a number passed as an argument, this is for avoid negatives values

Answer 2

I think your code is basically correct. The only issue is the sign:

select name,
       sum(case when money > 0 then money else 0 end) as sum_of_deposits,
       sum(case when money < 0 then -money else 0 end) as sum_of_withdrawals
from transfers 
group by name;

I do agree that filter is a more direct way of answering the question:

select name,
       sum(v) filter (where v > 0) as sum_of_deposits,
       sum(- v) filter (where v < 0) as num_of_withdrawals
from transfers
group by name;

But case is also a reasonable solution.

Answer 3

This example works with 'GROUP BY' feature. Your answer was so close to the solution but you have to keep in mind that 'CASE' function elaborate with all the values(=money) that passed. Not separated.

SELECT name, 
    sum(deposits) as sum_of_deposits, 
    sum(withdrawals) as sum_of_withdrawals
FROM (
    SELECT name,
        CASE 
            WHEN (money>0) THEN sum(money)
            ELSE (0)
        END as deposits,
        CASE 
            WHEN (money<0) THEN sum(abs(money))
            ELSE (0)
        END as withdrawals
    FROM transfers
    GROUP BY money
)
GROUP BY name;

Results : results.sqliteDB

Answer 4

COALESCE function can be used in order to replace null with 0

 select name,
 coalesce(sum(case when amount>0 then amount end), 0) as sum_deposits,
 coalesce(sum(case when amount<0 then amount end), 0) as sum_transfers
 from transfers
 group by name

Answer 5

--Try this, it will work

select name, sum (case when money >= 0 then money else 0 end) as sum_of_deposits, sum (case when money < 0 then ABS(money) else 0 end) as sum_of_withdrawals from transfers GROUP BY name order by name