Ali Madadi Ali Madadi - 1 year ago 176
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,

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

enter image description here

Answer Source

Try with inner join

        SELECT sum(pur.psum) - sum(sca.ssum) as quantities, p.scale, , 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 =  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, ORDER BY , pur.scale;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download