SQL substract values from same column different rows

Question

I have above mysql stock table where both inward and outward data are stored and identified each type by type column (i and o). I want to build a query to retrieve balance quantity(qty) per each batchno or drugid. for example, batch no 2001 has 1000 inward and 200 outward. So the balance should be 800. if there are many outwards for same batchno, they all should be included.

some help in this regard is highly appreciated.

Answer 1

retrieve balance quantity(qty) per each batchno or drugid

SELECT batchno, -- or drugid
       SUM(CASE type WHEN 'i' THEN qty  -- inward
                     WHEN 'o' THEN -qty -- outward
                     ELSE 0             -- unknown
           END) balance
FROM stock_table
GROUP BY batchno -- or drugid