user2997109 user2997109 - 6 months ago 12
SQL Question

Unable to get null values for the count per month

I am not sure what I am doing to get this wrong. I am trying to get the count of usertb.id per each month. I want the 0s to be displayed if the count is null. I am using a left outer join with a month table that consists just the id of the month and name of the month. Please help. I am using SQL SERVER 2008

SELECT monthTB.name
, subTB.name
, COUNT(userTB.ID)
FROM TDP_NetOps.dbo.sub_activity AS subTB
, TDP_NetOps.dbo.month AS monthTB
LEFT OUTER JOIN TDP_NetOps.dbo.user_sub_activity AS userTB ON monthTB.ID = DATEPART(month, userTB.date_of_activity)
WHERE subTB.sub_activity_id = userTB.sub_activity_id
AND userTB.emp_id = 'xxxx'
GROUP BY monthTB.name
, monthTB.ID
, subTB.name
ORDER BY monthTB.ID ASC
, subTB.name ASC

Answer

Well, there are a few things wrong with your query:

Firstly, do not use implicit JOIN syntax(comma separated) , use the proper syntax of join.

Secondly, if you do use them, don't ever mix them together with the explicit syntax, it will always lead to mistakes.

And lastly, conditions on the right table of a left join should be inside the ON clause, when specified in the where, the join turns into an inner join. :

SELECT monthTB.name
     , subTB.name
     , COUNT(userTB.ID)
FROM TDP_NetOps.dbo.sub_activity AS subTB
CROSS JOIN TDP_NetOps.dbo.month AS monthTB
LEFT OUTER JOIN TDP_NetOps.dbo.user_sub_activity AS userTB 
ON (monthTB.ID = DATEPART(month, userTB.date_of_activity)
    AND subTB.sub_activity_id = userTB.sub_activity_id
    AND userTB.emp_id = 'xxxx')
GROUP BY monthTB.name
       , monthTB.ID
       , subTB.name
ORDER BY monthTB.ID ASC
       , subTB.name ASC