GROUP BY missing some cells

J

James B

here is my query

SELECT
Sales.ProductId as productid,
SUM(Sales.Price) AS price,
SUM(Sales.Discount) AS discount,
count(*) AS item_count
FROM Payments
INNER JOIN Sales ON Payments.Id = Sales.PaymentId
WHERE
Payments.Paid <> 0 AND
Payments.Completed BETWEEN #"& curDateFormat &"# AND #"&
nextDateFormat &"#
GROUP BY productid;

I print a table with date ranges on the y axis and the
product ids on the x axis. The problem is if a product
had 0 payments in a given range it wouldn't return any
data thus making my table print incorrectly, is there a
way to instruct SQL to return 0 in these cases?

Please let me know if you need further explanation or an
example, thanks for your time and help.
 
M

[MVP] S.Clark

If you use an Outer Join from products to your current query, then you can
display ALL products, regardless if they had sales in that month.

Select Products.ProductName, [queryname].* FROM Products LEFT JOIN
[queryname] ON Products.ProductID = [queryname].ProductID;
 
Top