How to show zero count in SQL SERVER query
I want to know how we can display the amount of ZERO? I only have one table and I am doing a monthly count, but some like 1 month has no rows and SQL Server skips that month during counting, 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 looks like this, but it is missing the entry for the first month, 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 would be appreciated.
source to share
You can use a Tally ranging from 1 to 12 for a list of months, then follow LEFT JOIN
your request. Add an extra filter to N
only return records up to the current month.
Replace the content Cte
with the original request.
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())
source to share
It's weird, but you can 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)
source to share
Maybe this might be helpful for 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]
source to share
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
source to share