CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Mediasoft
Mediasoft

Reputation: 281

How to Show Zero Count in SQL SERVER Query

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.

Upvotes: 3

Views: 3618

Answers (4)

Gisha Ajeesh
Gisha Ajeesh

Reputation: 102

declare @year int
set @year = YEAR(GETDATE())

;with mths as(
    select 1 as mth, DATENAME(MONTH, cast(@year*100+1 as varchar) + '01')  as monthname
union all
    select mth+1, DATENAME(MONTH, cast(@year*100+(mth+1) as varchar) + '01') from mths where mth<MONTH(GETDATE()) 
)
select  m.mth Month, isnull(count,0) as Count
from    mths m 
left join (
    select   MONTH([createdDate]) as Month,COUNT([id]) as Count
    from     [Notifications]
    where    YEAR([createdDate]) = @year 

    group by MONTH([createdDate])
 ) s on m.mth = s.Month

Upvotes: 1

Mihir Shah
Mihir Shah

Reputation: 988

Probably this could be helpful to you.

Set Nocount On;

Declare  @LastMonth     Int = 1

Select  @LastMonth = Datepart(Month, Max(n.createdDate))
From    [PocketLife].[dbo].[Notifications] As n With (Nolock)

;With MonthCte As
(
    Select  1 As [Month]

    Union All

    Select  ([Month] + 1)
    From    MonthCte As m
    Where   m.[Month] < @LastMonth
)

Select   mc.[Month]
        ,ISNULL(COUNT(id), 0) [Count]
From    MonthCte As mc With (Nolock)
        Left Join [PocketLife].[dbo].[Notifications] As n On mc.[Month] = Datepart(n.createdDate)
Where   description = 'Welcome to Pocket Link'
        AND YEAR(CAST(createdDate as DATE)) = YEAR(GETDATE()
Group By mc.[Month]

Upvotes: 1

Felix Pamittan
Felix Pamittan

Reputation: 31879

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())

Upvotes: 4

maxymoo
maxymoo

Reputation: 36545

This is sort of weird, but you could just go

SELECT 0 [Month], 0 [Count]
UNION ALL
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)

Upvotes: 1

Related Questions