✅ GROUP BY and CASE WHEN ✅
IntuitionApproachUsed group by based on the stock name. Then used case when to get the sell and buy operation separately, used sum function to get the total pri
# Intuition
<!-- Describe your first thoughts on how to solve this problem. -->

# Approach
<!-- Describe your approach to solving the problem. -->
Used group by based on the stock name. Then used case when to get the sell and buy operation separately, used sum function to get the total price of sell and buy.
Used subraction to find the actual difference.


# Code
```postgresql []
-- Write your PostgreSQL query statement below
SELECT stock_name,
SUM(CASE WHEN operation = 'Sell' THEN price ELSE 0 END) - SUM(CASE WHEN operation = 'Buy' THEN price ELSE 0 END) AS capital_gain_loss
FROM stocks
GROUP BY stock_name

-- WITH sell AS (SELECT stock_name, operation, SUM(price) AS sell FROM stocks
-- WHERE operation = 'Sell'
-- GROUP BY stock_name, operation),
-- buy AS (SELECT stock_name, operation, SUM(price) AS buy
-- FROM stocks
-- WHERE operation = 'Buy'
-- GROUP BY stock_name, operation)
-- SELECT s.stock_name, s.sell - b.buy AS capital_gain_loss FROM sell s
-- LEFT JOIN buy b ON s.stock_name = b.stock_name
```
['PostgreSQL']