Count(*) grouped by month, with zero values if month has count 0

Question

I'm trying to get count from date for each month.

SELECT    COUNT(*) AS cnt, DATE_FORMAT(onBoardDateTime,'%Y-%m-01') AS date
FROM      paDatabase 
WHERE     YEAR(onBoardDateTime) > '2015'  
GROUP BY  MONTH(onBoardDateTime)

I want also include month with count = 0. So result should be:

Month | cnt
2015-01 | 10
2015-02 | 31
2015-03 | 0
2015-04 | 5
etc..

Thanks

Hi you will need to have a calendar table containing all the available months and `left join` that with your data to fill in the missing months. This might be of interest: http://www.plumislandmedia.net/mysql/filling-missing-data-sequences-cardinal-integers/ — IronMan, Aug 29 '19 at 22:04

score 0 · Answer 1 · answered Aug 29 '19 at 21:54

0

Try this:

SELECT    coalesce(COUNT(*), 0) AS cnt, DATE_FORMAT(onBoardDateTime,'%Y-%m-01') AS date
FROM      paDatabase 
WHERE     YEAR(onBoardDateTime) > '2015'  
GROUP BY  MONTH(onBoardDateTime)

(Not tested).

answered Aug 29 '19 at 21:54

gunnar2k

185
1
13

Hello, thanks for your response. I tried it but it makes no differences. – Frederik Fako Aug 29 '19 at 21:58
Oh well worth a shot :D – gunnar2k Aug 29 '19 at 22:06

slaakso · Answer 2 · 2019-08-29T22:04:35.890

You can only select data that exists in the database. To get the months with no data in it, list the months you are interested in and find those that do not have data. A common solution is to use a calendar table which list all the months / dates you need.

SELECT    COUNT(*) AS cnt, DATE_FORMAT(onBoardDateTime,'%Y-%m-01') AS date
FROM      paDatabase 
WHERE     YEAR(onBoardDateTime) > '2015'  
GROUP BY  DATE_FORMAT(onBoardDateTime,'%Y-%m-01')
UNION
SELECT    0, m
FROM CALENDAR_MONTHS
WHERE YEAR(m)>2015 AND m NOT IN (
   SELECT CAST(DATE_FORMAT(onBoardDateTime,'%Y-%m-01') as date) AS date
   FROM paDatabase 
   WHERE YEAR(onBoardDateTime) > '2015'  
);

Count(*) grouped by month, with zero values if month has count 0

2 Answers2