Let's say we have a Product table (product_id, product_name) and a Sales table (product_id, date, qty, amount).
What sql query would return the monthly sales per product, and include products with no sales?
(I'm using sql server if that makes any difference).
Clarification: I want a row for each possible month/product tuple. If there was no sales for a given month/product it should show zero qty/sales
Best Answer
See if this is what you're looking for (adjust as needed):
Note: The OP did not indicate they wanted a solution that takes the year into consideration, so my solution does not deal with the notion of a year when aggregating the data. If the month is January (regardless of year), the counts go in the January bucket.
UPDATE: To clarify a bit further, I want a row for each possible month/product tuple. If there was no sales for a given month/product it should show zero qty/sales