0

I want the count of my table data having in date range 7 days before from now. So I have tried this query :

SELECT DATE(leads_update_on), IFNULL(COUNT(*),0) leads 
    FROM tbl_leads 
    WHERE project_id=4 
    AND DATE(leads_update_on) >= DATE_SUB('2016-05-11', INTERVAL 6 DAY) 
    GROUP BY DATE(leads_update_on)

But it returns following result :

       `DATE(leads_update_on)|leads
       ----------------------|-----
       2016-05-06            |    7
       2016-05-07            |    4`

Since other dates does not have any data but I want the result like below if there is no data in specific date :

       `DATE(leads_update_on)|leads
       ----------------------|-----
       2016-05-05            |    0
       2016-05-06            |    7
       2016-05-07            |    4
       2016-05-08            |    0
       2016-05-09            |    0
       2016-05-10            |    0
       2016-05-11            |    0`

What I have to change in my sql query so that I can find the above result. Any help will be appreciated. Thanks in advance.

Sample Input as requested :

    `DATE                |id
   ----------------------|-----
   2016-05-06            |    1
   2016-05-07            |    2

Here only two data is present so for others dates it should return 0 value. It should output like this :

       `DATE(date)           |leads
       ----------------------|-----
       2016-05-05            |    0
       2016-05-06            |    1
       2016-05-07            |    1
       2016-05-08            |    0
       2016-05-09            |    0
       2016-05-10            |    0
       2016-05-11            |    0`

But using this query :-

SELECT DATE(`date`), IFNULL(COUNT(*),0) leads FROM test where DATE(`date`) >= DATE_SUB('2016-05-11', INTERVAL 6 DAY) GROUP BY DATE(`date`)

It returns below result which I don't want:

       `DATE(date)           |leads
       ----------------------|-----
       2016-05-06            |    1
       2016-05-07            |    1`
Rakesh Shetty
  • 4,548
  • 7
  • 40
  • 79
  • Here's a writeup of a solution to this problem. http://www.plumislandmedia.net/mysql/filling-missing-data-sequences-cardinal-integers/ – O. Jones May 11 '16 at 09:43

1 Answers1

0

from what I understand you need to change your if nullcondition

Updated

select distinct(res.leadDate), res.leads from        
(select mon.aDate as leadDate , ifnull(sa.leads, 0) as leads
  from (
     select '2016-05-11' - interval (a.a ) day as aDate 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) a
 ) mon
 left join tbl_leads sa on mon.aDate = sa.leads_date ) res, tbl_leads ss
 where res.leadDate between ss.leads_date and '2016-05-11'
 order by res.leadDate asc;
KP.
  • 393
  • 1
  • 12