Prashank Prashank - 1 month ago 9
MySQL Question

selecting count of user between two dates grouped by month.year and include zero for months where count=0

selecting count of user between two dates grouped by month.year and include zero for months where count=0.

Current Query :

select count(u_id) as users, monthname(register_date) as month_name,year(register_date) as year
from user
where register_date >= '2017-01-01' and register_date <= '2018-01-31'
group by year(register_date), month(register_date);


Returned Result:

users, month_name, year
'1', 'August', '2017'


Expected Output:

users, month_name, year
0 'Jan' '2017'
0 'Feb' '2017'
0 'March' '2017'
0 'April' '2017'
0 'May' '2017'
0 'June' '2017'
0 'July' '2017'
1 'Aug' '2017'
0 'Sept' '2017'
0 'Oct' '2017'
0 'Nov' '2017'
0 'Dec' '2017'
0 'Jan' '2018'


I Searched Many links but it does not fit my requirement for project.Please help.
Thanx in advance.

Answer Source

Create a table with all the dates, then left join what you already have.

select count(u.u_id) as users, monthname(z.Date) as 
month_name,year(z.Date) as year
from 
(
  select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) Month as Date
  from (select 0 as a union all 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) as a
  cross join (select 0 as a union all 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) as b
  cross join (select 0 as a union all 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) as c
) as z
left join user as u on z.Date = u.register_date

where z.Date >= '2017-01-01' and z.Date <= '2018-01-31'
group by year(z.Date), month(z.Date); 

Dates created like here