How to Show Zero Count in SQL SERVER Query

Question

I want to know that how we can display ZERO count? I have only one table and I am doing monthly count but some how 1 month doesn't have any rows and SQL Server is skipping that month during count but I need to show in my report.

This is my query that I am using:

SELECT 
    MONTH(createdDate) [Month], 
    ISNULL(COUNT(id), 0) [Count]
FROM 
    [PocketLife].[dbo].[Notifications]
WHERE 
    description = 'Welcome to Pocket Link' AND 
    YEAR(CAST(createdDate as DATE)) = YEAR(GETDATE())
GROUP BY 
    MONTH(createdDate)

Currently the above query is showing like this but it is missing the First Month Record which is ZERO.

Month   Count
--------------
2            5 
3          295 
4         8295 
5       149855 
6       447752 
7         6311 

But it should Show as below and this is the actual Result:

Month   Count
--------------
1            0 
2            5 
3          295 
4         8295 
5       149855 
6       447752 
7         6311 

Any help will be appreciated.

Answer 1

You can use a Tally Table ranging from 1 - 12 for the list of months, then do a LEFT JOIN on your query. Add an additional filter for N so that it only returns records up to the current month.

Replace the content of Cte with your original query.

WITH Cte([Month], [Count]) AS(
    SELECT 2, 5 UNION ALL
    SELECT 3, 295 UNION ALL
    SELECT 4, 8295 UNION ALL
    SELECT 5, 149855 UNION ALL
    SELECT 6, 447752 UNION ALL
    SELECT 7, 6311
),
CteTally(N) AS(
    SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL 
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
    SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL 
    SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
)
SELECT
    t.N AS [Month],
    ISNULL(c.Count, 0) AS [Count]
FROM CteTally t
LEFT JOIN Cte c
    ON t.N =c.Month
WHERE
    t.N <= MONTH(GETDATE())