GROUP BY missing some cells

  • Thread starter Thread starter James B
  • Start date Start date
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.
 
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;
 
Back
Top