Re: sum, group by, union

[Chris Stubben]

Hi,

Since products may or may not have matching rows in the delivery and sell table, you need to use left joins. Also, since left joins will return NULLs for non-matching rows, you want to convert these to zeros using the ifnull function in your SELECT query.

try this.

select p.name, sum(ifnull(d.Count, 0)), sum(ifnull(s.Count, 0))
from TblProduct p
left join TblProductDelivery d on p.Number=d.ProductNumber
left join TblProductSell s on p.Number=d.ProductNumber
group by p.name;

Chris