> i have tried > select > Seller.NameSeller, > sum(buyfromseller.qty*buyfromseller.cost) as buys, > sum(sell.qtysell*sell.price) as Sells, > from > Seller > left join buyfromseller on buyfromseller.fkseller=seller.idseller > left join sell on sell.fkseller=seller.idseller group by seller.nameseller > > but it turn me doubles values for the sell sum, how can i resolve it?
This is not a case where you want to use JOIN. This is more what you want: SELECT Seller.NameSeller, COALESCE( (SELECT SUM(buyfromseller.qty*buyfromseller.cost) FROM buyfromseller WHERE buyfromseller.fkseller=seller.idsellersum), 0) as buys, COALESCE( (SELECT SUM(sell.qtysell*sell.price)) FROM Sell WHERE sell.fkseller=seller.idseller), 0) as Sells FROM Seller ORDER BY seller.nameseller
