How to force SQL Join to count NULL values

Question

Apologies for the bad title, im at the beginner level.

The issue is this, i have a table of sold item counts, grouped by item type, month and year. Now if i run the query below:

SELECT YEAR(S.SellDate) AS Year, 
       MONTH(S.SellDate) AS Month, 
       T.ID as ProdType, 
       T.[Description] as ProdDescription, 
       COUNT(T.ID) AS Sold
FROM Items S
       RIGHT JOIN Types T on S.TypeID = T.ID
WHERE YEAR(S.SellDate) = 2022 
GROUP BY YEAR(SellDate), MONTH(SellDate), T.ID, T.[Description]
ORDER BY 1, 2, 3 ASC

I get the following table, notice how there are "missing" rows due to that type of item not being sold. I've tried different join types to no avail. The goal would be to list all items types with NULL or 0 if there no sale of that item was made.

Result:

The goal would be

Thanks in advance.

Outer join a calendar table/cte to get missing months too. – jarlh Mar 08 '23 at 10:23 — jarlh, Mar 08 '23 at 10:23

score 0 · Answer 1 · answered Mar 08 '23 at 10:49

Try using this. SQL Server JOIN missing NULL values

SELECT YEAR(S.SellDate) AS Year, MONTH(S.SellDate) AS Month, T.ID as ProdType, T.[Description] as ProdDescription, COUNT(T.ID) AS Sold

FROM Items S left JOIN Types T on S.TypeID = T.ID WHERE YEAR(S.SellDate) = 2022

GROUP BY YEAR(SellDate), MONTH(SellDate), T.ID, T.[Description]

ORDER BY 1, 2, 3 ASC

How to force SQL Join to count NULL values

1 Answers1