Francisco Plácido Francisco Plácido - 4 days ago 6
SQL Question

How to add the weeks with no items sold as zero values to my SQL query?

Basically I have this query below, that gives me the number of sales per week, but it skips the weeks where there were no sales for a specific item. I would like it also to display those as 0 values.
And by the way, is it possible to merge the two queries below? I tried but couldn't get the joins to work.

SELECT tblWeek.WeekNum, [Query - Sales].ModelName, Count([Query - Sales].OutDate) AS CountOfOutDate
FROM tblWeek LEFT JOIN [Query - Sales] ON tblWeek.WeekNum = [Query - Sales].WeekNumber
GROUP BY tblWeek.WeekNum, [Query - Sales].ModelName
HAVING ((([Query - Sales].ModelName) Is Not Null))
ORDER BY [Query - Sales].ModelName;


Query - Sales:

SELECT DDL_Items.ModelName, DDL_Items.OutDate, DDL_Models.Type, DatePart("ww",[OutDate],2) AS WeekNumber
FROM DDL_Models INNER JOIN DDL_Items ON DDL_Models.ModelName = DDL_Items.ModelName;


enter image description here

Thank you and best regards,

Francisco

Answer

The first thing you want to do is create a Cartesian Join between your model name and the weeks. Something like:

SELECT DISTINCT [Query - Sales].ModelName, tblWeek.WeekNum
FROM [Query - Sales]
LEFT JOIN tblWeek 

This will create one record for every week for every model name.

Then, you want to join the actual data to this query:

SELECT qryA.*, Count([Query - Sales].OutDate) AS CountOfOutDate
FROM (
SELECT DISTINCT [Query - Sales].ModelName, tblWeek.WeekNum
FROM [Query - Sales]
LEFT JOIN tblWeek 
) as qryA
LEFT JOIN [Query - Sales]   
   ON tblWeek.WeekNum = qryA.WeekNumber
GROUP BY tblWeek.WeekNum, qryA.ModelName
HAVING qryA.ModelName Is Not Null
ORDER BY qryA.ModelName;
Comments