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

114
August 16, 2017, at 8:33 PM

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 1

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

select count(u_id) as users, monthname(a.Date) as 
month_name,year(a.Date) as year
from 
(
  select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY 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 a
left join user as u on a.Date = u.register_date
where a.Date >= '2017-01-01' and a.Date <= '2018-01-31'
group by year(a.Date), month(a.Date); 

Dates created like here

Answer 2

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
Answer 3

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

READ ALSO
mysql result conversion to pivot table with php

mysql result conversion to pivot table with php

I have defined MYSQL result as an array definitionThe problem is i have to concatenate same attribute with different values for the same product with comma(;)

194
Ordening search results per VAT rate

Ordening search results per VAT rate

I have a table named invoiceItemsIt contains individual lines of an invoice

168
Search along route using mysql [on hold]

Search along route using mysql [on hold]

Need to search along route, i have start point lat,long and end point lat,long and radius tooI have to search to all records in between these points with in my database, need your suggestions

218
Is this query vulnerable to SQL Injection attacks [duplicate]

Is this query vulnerable to SQL Injection attacks [duplicate]

This question already has an answer here:

164