enter image description here

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.

1 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
  • thank you very much. it works as I expected.
    – amsandun
    May 22, 2019 at 10:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.