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
– jarlh

2023-03-08 10:23:18 +00:00
Commented Mar 8, 2023 at 10:23 — jarlh
– jarlh, Commented Mar 8, 2023 at 10:23

Maddy · Accepted Answer · 2023-03-08 10:49:27Z

0

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

answered Mar 8, 2023 at 10:49

Maddy

875 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to force SQL Join to count NULL values

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related