SQL - How to subtract values from one column-CodePudding

In the table below I'd like to subtract the total shares bought witht the total shares sold for each symbol.

TABLE transactions
 -------- -------- -------------------- -------- --------- --------- --------------------- 
| t_type | symbol |        name        | shares | t_price | t_value |       t_date        |
 -------- -------- -------------------- -------- --------- --------- --------------------- 
| buy    | aapl   | Apple Inc          | 10     | 159.61  | 1596.1  | 2022-02-24 18:51:22 |
| buy    | gold   | Barrick Gold Corp. | 20     | 22.735  | 454.7   | 2022-02-24 18:51:44 |
| sell   | aapl   | Apple Inc          | 5      | 158.715 | 793.575 | 2022-02-24 19:04:51 |
| sell   | gold   | Barrick Gold Corp. | 2      | 22.54   | 45.08   | 2022-02-25 10:15:29 |
| sell   | gold   | Barrick Gold Corp. | 1      | 22.86   | 22.86   | 2022-02-25 10:16:04 |
 -------- -------- -------------------- -------- --------- --------- ---------------------

For example id I do first:

SELECT t_type, symbol, shares FROM transactions WHERE user_id = 14 AND t_type = "buy";
 -------- -------- -------- 
| t_type | symbol | shares |
 -------- -------- -------- 
| buy    | aapl   | 10     |
| buy    | gold   | 18     |
 -------- -------- --------

and then

SELECT t_type, symbol, shares FROM transactions WHERE user_id = 14 AND t_type = "sell";
 -------- -------- -------- 
| t_type | symbol | shares |
 -------- -------- -------- 
| sell   | aapl   | 5      |
| sell   | gold   | 2      |
| sell   | gold   | 1      |
 -------- -------- --------

Is there a way to subtract the shares?

CodePudding user response：

You can try to use condition aggregate function, SUM & CASE WHEN with your logic, buy be positive stock, sell be negative stock

SELECT  symbol,SUM(CASE WHEN t_type  = 'buy' THEN shares 
                               WHEN t_type  = 'sell' THEN - shares 
                               ELSE 0 END)
FROM transactions 
WHERE user_id = 14
GROUP BY  symbol

Results:

| symbol | totalshares |
|--------|-------------|
|   aapl |           5 |
|   gold |          17 |