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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Thinker
  • 518
  • 1
  • 6
  • 22
  • Possible duplicate of [MySQL monthly Sale of last 12 months including months with no Sale](https://stackoverflow.com/questions/27600863/mysql-monthly-sale-of-last-12-months-including-months-with-no-sale) – inetphantom Aug 16 '17 at 13:17

3 Answers3

1

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

inetphantom
  • 2,498
  • 4
  • 38
  • 61
  • can you be little bit more specific ? What will years table include? years upto which limit – Thinker Aug 16 '17 at 13:09
  • @Prashank That is up to you. Where do you expect the years to come from? – inetphantom Aug 16 '17 at 13:11
  • From passed startdate and enddate .Above query is inside a procedure. – Thinker Aug 16 '17 at 13:12
  • So in that case row should be 0 June 2017 – Thinker Aug 16 '17 at 13:16
  • Above solution does not give expected result for start date '2012-01-01' end date '2018-01-31'. – Thinker Aug 16 '17 at 18:30
  • @Prashank Addressed that: Had the last 999 Days instead of the last 999 Month. – inetphantom Aug 17 '17 at 06:21
  • And joining for every Query i dont think is efficient as it can slower down the performance instead we can create a temporary table for dates and select from that table . I am using this in my project ? Any comments? – Thinker Aug 17 '17 at 07:21
  • @Prashank It does not matter if you left join or union because of the group. it will always have to check for duplicates. See the link, it is not that inperformant. But sureley you can instead of the calculation select do a real select, but normaly creating data into RAM is faster than a read from DB – inetphantom Aug 17 '17 at 07:45
  • ohkh, Right now what i am doing i created a table date_table with two field date :primary key and user_count column and inserted rows for one year i.e 2017 and update user_count for date curdate- day and every midnight i am updating usercount for current date . with this table all i need to do i simple select. updating usercount at midnight will does through mysql scheduled job. – Thinker Aug 17 '17 at 08:05
  • hey i tested seprated table strategy it took 0.00044 sec / 0.000023 duration for 1 year of data. and join table strategy took 0.7 sec for just generating dates . Note: Test performed using mysql-workbench. And thanx for the support. – Thinker Aug 17 '17 at 09:42
-1

Please try this query :

DECLARE @FromDate DATETIME, @ToDate DATETIME;
SET @FromDate = '2013-01-01';
SET @ToDate = '2014-12-31';


SELECT TOP (DATEDIFF(MONTH, @FromDate, @ToDate)+1)
 MONTH(DATEADD(MONTH, number, @FromDate)) TheMonth,
  YEAR(DATEADD(MONTH, number, @FromDate)) TheYear
  INTO Data
  FROM [master].dbo.spt_values 
  WHERE [type] = N'P' ORDER BY number;

select * from Data 

select  Data.TheMonth,Data.TheYear,  Isnull(tmp.users,0) as usercount
from    Data 
        LEFT OUTER JOIN (select count(1) as users, month(register_date) as month_name,year(register_date) as year 
                        from    user 
                        where   CONVERT(nvarchar(121), register_date , 121) >=CONVERT(nvarchar(121),@FromDate  , 121) 
                                AND CONVERT(nvarchar(121), register_date , 121) <=CONVERT(nvarchar(121), @ToDate  , 121) 
                         group by year(register_date), month(register_date) ) tmp on tmp.month_name = Data.TheMonth AND tmp.year = Data.TheYear

DROP TABLE Data
Bhavika Zimbar
  • 411
  • 1
  • 5
  • 19
-1

Please create a temporary table(#temp_tbl) which has all your dates, then the following query should produce the result you are expecting :

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)
union
 select '0' as users, 
         monthname(register_date) as month_name,
         year(register_date) as year 
    from #temp_tbl 
    where  register_date >= '2017-01-01' and register_date<='2018-01-31'

Regards, Abdulla

MJK
  • 3,434
  • 3
  • 32
  • 55
Abdulla P
  • 1