[SOLVED] subtract two column from different table and group by

Issue

after one week search i couldn’t write this query please help me.
i have 3 table purchase, selles, stuff.
i want to first sum purchase.quantity selles.quantity fields separately and then subtract these two column purchase.quantity selles.quantity then GROUP BY purchase.scale, stuff.name

---

---

---

i have these 3 table

table purchase:
   'id', 'invoice_id', 'stuff_id', 'quantity', 'scale', 'price'
table selles:
   'id', 'selles_invoice_num', 'stuff_id', 'quantity', 'scale', 'price'
table stuff:
   'id', 'name'

---

i have this query but result (quantities) is wrong

SELECT sum(purchase.quantity) - sum(selles.quantity) as quantities, 
       purchase.scale, 
       stuff.name , 
       stuff.id 
FROM purchase, 
     selles, 
     stuff 
where purchase.stuff_id = selles.stuff_id 
and purchase.scale = selles.scale 
and purchase.stuff_id = stuff.id 
group by purchase.scale, 
         stuff.name 
ORDER BY stuff.name , 
         purchase.scale

Solution

Try with inner join

        SELECT sum(pur.psum) - sum(sca.ssum) as quantities, p.scale, st.name , st.id FROM
 stuff st INNER JOIN  
    (SELECT sum(p.quantity) as psum,p.scale,p.stuff_id  FROM purchase p GROUP BY p.scale,p.stuff_id) pur 
on pur.stuff_id = st.id  INNER JOIN 
    (SELECT sum(s.quantity) as ssum,s.scale,s.stuff_id  FROM scale s GROUP BY s.scale,s.stuff_id) sca
 on sca.scale=pur.scale and sca.stuff_id=pur.stuff_id  group by pur.scale, st.name ORDER BY st.name , pur.scale;

Answered By – Mahesh Madushanka

Answer Checked By – Marie Seifert (BugsFixing Admin)