Difference between rows with the same value

Question

I have the table

create table fct_bonus (
    date timestamp not null,
    type varchar(10) not null,
    amount numeric(19,2) not null,
    userid varchar(30) not null
)

type can be IN or OUT, amount is always >0

I need to find sums of ins and outs for userid 123 on date 2016-08-01', and also the balans, which should be count as all ins minus all outs of userid123. I use the query

select distinct userid, type, sum(amount)
from fct_bonus
where userid = 123 and date <= '2016-08-01'
group by type

but I don't know, how to count the balans. Please, help.

Answer 1

This would seem to do what you are describing:

select userid,
       sum(case when type = 'IN' then 1 else 0 end) as ins,
       sum(case when type = 'OUT' then 1 else 0 end) as outs,
       sum(case when type = 'IN' then amount when type = 'OUT' then - amount end) as balance
from fct_bonus
where userid = 123 and date <= '2016-08-01'
group by userid;