CODEWITHSUNDEEP
sqlselectsum
kashimu
kashimu

Reputation: 174

Select SUM from two other tables

I have this query and successfully gets the totstock

SELECT p.pid,p.product_name,SUM(s.qty) as totstock
FROM tblproducts p
LEFT JOIN tblstocks s ON s.pid=p.pid
GROUP BY p.pid

but when I tried to join my second table it gets wrong total totstock and totsales I have this query but i think it is wrong

SELECT p.pid,p.product_name,SUM(s.qty) as totstock,SUM(sl.qty) as totsale
FROM tblproducts p
LEFT JOIN tblstocks s ON s.pid=p.pid
LEFT JOIN tbls sl ON sl.pid=p.pid
GROUP BY p.pid

Products - tblproducts

pid  |   product_name
 1   |  pencil
 2   |  paper

Stocks - tblstocks

 pid  |  qty 
 1    |   1
 1    |   3
 1    |   5

Sales - tbls

 pid  |  qty
  1   |   2
  1   |   1

The Result i want is

pid  | name    | totstock | totsales
 1   |  pencil |    9     |    3  
 2   |  paper  |  NULL    |  NULL

Upvotes: 0

Views: 95

Answers (2)

Sunil Tandon
Sunil Tandon

Reputation: 151

Try a group by on p.product_name also. I think that will fix the issue.

Upvotes: 0

Amit Singh
Amit Singh

Reputation: 8109

SELECT p.pid,p.product_name,totstock, totsale
FROM tblproducts p
LEFT JOIN (Select pid, Sum(qty) as totstock from tblstocks group by pid)  s ON s.pid=p.pid
LEFT JOIN (Select pid, Sum(qty) as totsale from tbls group by pid) sl ON sl.pid=p.pid

Sql Fiddle Demo

Upvotes: 3

Related Questions