'SQL - How to subtract values from one column

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?

sqlsqlite


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions