SQL How can I use GROUP BY with conditions such as "WHERE 'something' is same"-CodePudding

Is there a way to subtract sum in SELL row from sum in BUY row, but only for rows with the same symbol? At this point I am clueless of what to do next. Maybe something like ""WHERE 'something' is same, subtract these rows"

This is what I have:

SELECT type, symbol, SUM(count) AS sum 
FROM transactions 
WHERE user_id = 1 
GROUP BY type, symbol;

And the result:

 ------ -------- ----- 
| type | symbol | sum |
 ------ -------- ----- 
| BUY  | AAPL   | 3   |
| BUY  | NFLX   | 6   |
| BUY  | TSLA   | 13  |
| SELL | NFLX   | 2   |
 ------ -------- -----

So, for example, the final query should output that I have 4 NFLX shares.

Also, here are all columns names: id, user_id, type, symbol, count, price_original, timestamp

CodePudding user response：

The expression you are aggregating with SUM() can be a more complex expression instead of a simple column. For example:

select
  symbol,
  sum(case when type = 'BUY' then count else -count end) as sum
from transactions
where user_id = 1
group by symbol