SQL: sum rows with conditions

Question

I have a table in the database as follows:

date       account      side     size     
2022-01-01 1            buy      50
2022-01-01 1            sell     25
2022-01-01 1            buy      35
2022-01-01 1            sell     10
2022-01-01 2            buy      100
2022-01-01 2            sell     50
2022-01-02 1            buy      10
2022-01-02 1            sell     10
2022-01-02 2            buy      100
2022-01-02 2            sell     10

What I want is to subtract the size when the side is 'sell' and add it when it is buy to have something like this:

date       account      volume
2022-01-01 1            50
2022-01-01 2            50
2022-01-02 1            0  
2022-01-02 2            90

I have tried the following

select date, account, sum(case when size='sell' then size = -size else size = size end) as volume
group by date, account, side, size

EDIT: select date, account, sum(case when side='sell' then size = -size else size = size end) as volume group by date, account, side, size

But I get an error: ERROR: function sum(boolean) does not exist. HINT: No function matches the given name and argument types. You might need to add explicit type casts.

What am I doing wrong?

Answer 1

Conditional aggregation is the way to go here, but your logic is a bit off. Also, you want to aggregate by only the date and account.

SELECT date, account,
       SUM(CASE WHEN size = 'buy' THEN size ELSE -size END) AS volume
FROM yourTable
GROUP BY date, account
ORDER BY date, account;