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


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

enter image description here

enter image description here

enter image description here

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, 
       stuff.name , 
FROM purchase, 
where purchase.stuff_id = selles.stuff_id 
and purchase.scale = selles.scale 
and purchase.stuff_id = stuff.id 
group by purchase.scale, 
ORDER BY stuff.name , 

enter image description here


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)

Leave a Reply

Your email address will not be published. Required fields are marked *