Ali Madadi Ali Madadi - 4 months ago 14
SQL Question

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,
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


enter image description here

Answer

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;
Comments