SQL Server: group by to retrieve rows with 0

Question

I have a query which retrieves a list of count grouped by month as below

 SELECT CASE WHEN MONTH(StartDate) = 1 THEN 'January' WHEN MONTH(StartDate) = 2 then 'February' WHEN MONTH(StartDate) = 3 then 'March' WHEN MONTH(StartDate) = 4 then 'April' WHEN MONTH(StartDate) = 5 then 'May' WHEN MONTH(StartDate) = 6 then 'June' WHEN MONTH(StartDate) = 7 then 'July' WHEN MONTH(StartDate) = 8 then 'August' WHEN MONTH(StartDate) = 9 then 'September' WHEN MONTH(StartDate) = 10 then 'October' WHEN MONTH(StartDate) = 11 then 'November' WHEN MONTH(StartDate) = 12 then 'December' ELSE '' END AS [month], COUNT(*) AS Count FROM Users WHERE YEAR(StartDate) = '2018' GROUP BY MONTH(StartDate) ORDER BY MONTH(StartDate)

The result looks something like this:

Month | Count ---------+------- January | 1 February | 2 April | 7 May | 3

As you can see there are missing months on the list which id like to retrieve. Expected result:

Month | Count ---------+------- January | 1 February | 2 March | 0 April | 7 May | 3 June | 0

Etc...

How can I get the months with zero count?

Why don't you just use DATENAME(MONTH, StartDate) AS Month instead of that big, unwieldy CASE expression..... — marc_s
– marc_s, Commented Nov 14, 2018 at 12:17

D-Shih · Accepted Answer · 2018-11-14 12:34:48Z

You can try to make a calendar table then do outer join it.

and move condition YEAR(StartDate) = '2018' to ON from WHERE , because you need to use OUTER JOIN

and just order by startnum column.

;WITH CTE AS ( SELECT 1 startnum,12 endnum UNION ALL SELECT startnum +1 ,endnum FROM CTE where startnum +1 <= endnum ) SELECT t1.dt as [month], Count(Users.StartDate) as Count FROM ( SELECT DATENAME(month, DATEADD(month, startnum - 1, CAST('1900-01-01' AS date))) dt,startnum FROM CTE ) t1 LEFT JOIN Users on t1.startnum = MONTH(StartDate) AND YEAR(StartDate) = '2018' GROUP by t1.dt,t1.startnum ORDER BY t1.startnum

sqlfiddle

Hi - I just tried your example and it retrieves a list but the months with zeros are still missing
Btw the order by is not working properly, can't seem to know why? prnt.sc/li8wst
@MishMish If order by name of month it will order by the character I edit my answer :)

Yogesh Sharma · Accepted Answer · 2018-11-14 12:00:45Z

You can use values construct & do left join :

SELECT mm.month, count(q.Count) FROM ( VALUES ('January'), . . . , ('December') ) mm (month) LEFT JOIN ( <query> ) q ON q.month = mm.month;

Gordon Linoff · Accepted Answer · 2018-11-14 12:02:05Z

Generate a list of month start dates and use those in the query:

with dates as ( select cast('2018-01-01' as date) as dte, 1 as num union all select dateadd(month, 1, dte), num + 1 from dates where num < 12 ) select year(d.dte), datename(month, d.dte), count(u.startdate) from dates d left join users u on month(u.startdate) >= month(d.dte) where u.startdate >= '2018-01-01' and u.startdate < '2019-01-01' group by d.dte order by d.dte;

This generates the months using a recursive CTE. Note some of the other changes:

The year is explicitly included throughout the query, to ensure that the query continues to do what you expect even if expand the date range.
The date range is defined using a range, rather than year(). This is a best practice, so the query can take advantage of indexes.
There is no need for a case expression, because date functions can extract the month name.

Collectives™ on Stack Overflow

SQL Server: group by to retrieve rows with 0

3 Answers 3

7 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Related