3

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

4

See if this is what you're looking for (adjust as needed):

set nocount on

--Declare a months table (or use a Date Dimension table)
Declare @Months Table (MonthNumber tinyint, MonthName varchar(20))
insert into @Months (MonthNumber,MonthName) values
(1,'January'),
(2,'February'),
(3,'March'),
(4,'April'),
(5,'May'),
(6,'June'),
(7,'July'),
(8,'August'),
(9,'September'),
(10,'October'),
(11,'November'),
(12,'December')

--Declare a products table
Declare @Products table (Id int, ProductName Varchar(20)) 
insert into @Products (Id, ProductName)
values(1,'Widgets'),(2,'Thingamabob')

Declare @Sales table (product_id int, [date] Date, qty int, amount decimal(11,2))
insert into @Sales (product_id, [date], qty, amount)
values(1,'2017-01-01',1,100.00),(2,'2017-02-01',1,200.00),(2,'2017-06-01',2,300)

--Select Months and CROSS JOIN to Products, then
--LEFT JOIN to Sales.  Use ISNULL to return zeros
--for months/products with no sales
SELECT m.MONTHNUMBER
    ,m.MonthName
    ,P.ProductName
    ,ISNULL(SUM(QTY), 0) AS QTY
    ,ISNULL(SUM(AMOUNT), 0) AS AMOUNT
FROM @Months m
CROSS JOIN @Products p
LEFT JOIN @Sales s ON DatePart(MONTH, [date]) = M.MonthNumber
    AND S.product_id = P.ID
GROUP BY m.MonthNumber
    ,m.MonthName
    ,P.ProductName
ORDER BY M.MonthNumber
    ,m.MonthName
    ,P.ProductName

| MONTHNUMBER | MonthName | ProductName | QTY | AMOUNT |
|-------------|-----------|-------------|-----|--------|
| 1           | January   | Thingamabob | 0   | 0.00   |
| 1           | January   | Widgets     | 1   | 100.00 |
| 2           | February  | Thingamabob | 1   | 200.00 |
| 2           | February  | Widgets     | 0   | 0.00   |
| 3           | March     | Thingamabob | 0   | 0.00   |
| 3           | March     | Widgets     | 0   | 0.00   |
| 4           | April     | Thingamabob | 0   | 0.00   |
| 4           | April     | Widgets     | 0   | 0.00   |
| 5           | May       | Thingamabob | 0   | 0.00   |
| 5           | May       | Widgets     | 0   | 0.00   |
| 6           | June      | Thingamabob | 2   | 300.00 |
| 6           | June      | Widgets     | 0   | 0.00   |
| 7           | July      | Thingamabob | 0   | 0.00   |
| 7           | July      | Widgets     | 0   | 0.00   |
| 8           | August    | Thingamabob | 0   | 0.00   |
| 8           | August    | Widgets     | 0   | 0.00   |
| 9           | September | Thingamabob | 0   | 0.00   |
| 9           | September | Widgets     | 0   | 0.00   |
| 10          | October   | Thingamabob | 0   | 0.00   |
| 10          | October   | Widgets     | 0   | 0.00   |
| 11          | November  | Thingamabob | 0   | 0.00   |
| 11          | November  | Widgets     | 0   | 0.00   |
| 12          | December  | Thingamabob | 0   | 0.00   |
| 12          | December  | Widgets     | 0   | 0.00   |

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

  • Perfect! Any reason why you use cross apply instead of cross join here? – Clement Oct 18 '17 at 19:17
  • 1
    @Clement - you are correct - CROSS JOIN better matches my intentions - I have updated my answer. – Scott Hodgin Oct 18 '17 at 19:23
1

This is what a LEFT JOIN does.

Getting every row from the left table and the matched rows from the right table. For any rows of the left table that does not have a match on the right one (in your case, for every product with no sales) you will get NULL values.

The following code:

with products (id, name) as 
(
    select 1, 'p1' union all
    select 2, 'p2' union all
    select 3, 'p3'
)
, sales (pid, date, qty, amount)  as
(
    select 1, '20170101', 3, 100  union all
    select 2, '20170101', 4, 200  union all
    select 2, '20170102', 5, 500 

)

select p.id, p.name, sales.date, sales.qty, sales.amount
from products p left join  sales on p.id = sales.pid                           
order by p.id, sales.date

Generates the following result:

enter image description here

For product id = 3, there are no sales in the source data but you still see the product listed in the results.

EDIT:

Consider making use of the calendar table from Aaron Bertrand's article. You can check out a working demo here.

You have a list of month, product and sales for each month of 2017, for each product.

  • Sorry if i wasn't clear enough. 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 – Clement Oct 18 '17 at 7:10
  • You need a list of 12 month per year? You will need a date calendar for that. The left join logic still stands :) – Rigerta Demiri Oct 18 '17 at 7:17
  • I'm ok with a calendar table. However I don't see how your answer solves the problem. For a start there is no month grouping. – Clement Oct 18 '17 at 9:23
  • @Clement it might be good to add these clarifications as an edit to your question. – LowlyDBA Oct 18 '17 at 13:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.