(psql) How to sum up on a column based on conditions?

Question

I have a following postgresql table.

user	amount	type
danny	2	deposit
danny	3	withdraw
kathy	4	deposit
kathy	5	deposit
kathy	6	withdraw

Now, I am trying to get every user's remaining wallet balance. The sum up calculation works like this: for deposits they are positive in the sum function and for withdraw they are negative values. e.g. For danny, the remaining balance after 2 deposit and 3 withdraw is 2 - 3 = -1. For kathy, the remaining balance is 4 + 5 - 6 = 3.

What is the easiest way to calculate this in one Postgresql query?

Thanks.

Answer 1

Convert the type from text to the numeric factor 1 or -1 as appropriate. Then just do sum(amount * factor):

with test (usr, amount, type) as 
     ( values ( 'danny', 2, 'deposit')
            , ( 'danny', 3, 'withdraw')
            , ( 'kathy', 4, 'deposit')
            , ( 'kathy', 5, 'deposit')
            , ( 'kathy', 6, 'withdraw')
     )
-- your query starts here
select usr                   "User"
     , sum (amount * factor) "Balance"
  from ( select usr
              , amount
              , case when type = 'deposit'  then  01 
                     when type = 'withdraw' then -1
                     else null 
               end factor
          from test
       ) sq 
 group by usr 
 order by usr;

NOTE: It is poor practice to use user as an identifier (i.e. column name, etc) since it is both a Postgres and SQL standard reserved word.