| tid | stock | sid | sval | type |
|---|---|---|---|---|
| 1 | abc | 1 | 100 | buy |
| 2 | abc | 2 | 120 | buy |
| 3 | xyz | 3 | 300 | buy |
| 4 | abc | 4 | 240 | sell |
| 5 | xyz | 5 | 250 | sell |
Query to get the following output
Output
| stock | profit/loss |
|---|---|
| abc | 20 |
| xyz | -50 |
CodePudding user response：
You can use a case expression to count "sell"s as profits and "buy"s as loses, and then sum them:
SELECT stock, SUM(CASE type WHEN 'sell' THEN sval
WHEN 'buy' THEN sval * -1
END) as "profit/loss"
FROM stocks
GROUP BY stock