[MySQL] Single group by + SUM solution.
Let\'s say all buy/sell pairs are represented as (b1, s1), (b2, s2), ... (bn, sn) then what we are looking for is (s1 - b1) + (s2 - b2) + ... (sn - bn). Because
Let\'s say all buy/sell pairs are represented as `(b1, s1), (b2, s2), ... (bn, sn)` then what we are looking for is `(s1 - b1) + (s2 - b2) + ... (sn - bn)`. Because of associativity we can remove all parenthesis and shuffle all operands any way we want without affecting the final sum, so there is no need to pick up the buy price for the corresponding sell price.\n\n```\nSELECT stock_name, SUM(\n    CASE\n        WHEN operation = \'Buy\' THEN -price\n        ELSE price\n    END\n) AS capital_gain_loss\nFROM Stocks\nGROUP BY stock_name\n```
[]