How to get separate sums of last seven days from SQL with 0 value included

Question

In my CRM system I have table with leads. I would like to make a chart to see how many leads were added in last 7 days. For that purpose I need to have separete sums for every day from last week.

My table called tab_leads comes with lead_id (integer) and lead_create_date (time stamp, format: 0000-00-00 00:00:00)

So I need something like:

• Day 1 - 10
• Day 2 - 0
• Day 3 - 5
• Day 4 - 0
• Day 5 - 9
• Day 6 - 15
• Day 7 (today) - 0

At the moment I am usign this query:

SELECT
    DATE(lead_create_date) AS `Date`,
    COUNT(*) AS `Leads`
FROM
    tab_leads
WHERE
    lead_create_date >=  CURRENT_DATE - INTERVAL 6 DAY
GROUP BY
    DATE(lead_create_date)

But the problem is, that if in any of those days we do not hava any data (ex. weekend) I am getting less than 7 sums. Ex:

• Day 1 - 10
• Day 2 - 5
• Day 3 - 9
• Day 4 - 15

For drawing a chart I need to have always seven sums, even with 0 value. How to do that in MySQL or MySQL + PHP?

..UPDATE: I am just trying to create SQL Fiddle withous success. Sample data:

CREATE TABLE tab_leads (
  `lead_id` int,
  `lead_create_date` timestamp
) ENGINE=InnoDB 

INSERT INTO tab_leads
  (`lead_id`, `lead_create_date`) 
VALUES
(0, '2015-05-02 05:30:40'),
(1, '2015-05-02 00:00:00'),
(2, '2015-05-03 00:00:00'),
(3, '2015-05-03 00:00:00'),
(4, '2015-05-05 00:00:00'),
(5, '2015-05-06 00:00:00'),
(6, '2015-05-07 00:00:00'),
(7, '2015-05-08 00:00:00'),
(8, '2015-05-08 00:00:00')
;

Answer 1

Because in your original table there is no data for the given dates, you can't just return a row with 0. You may see common examples such as:

Give the count of each [some field] for each user, even if it is 0.

These examples can be done, assuming there is a record for each user somewhere, even if there isn't a record for some field. In your case, you don't have a record for days 2, 4, or 7, so they can't be returned.

To work around this, you need to create a temporary table of the last 7 days. Using an example given here:

select a.Date 
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
) a
where a.Date between DATE_SUB(NOW(), INTERVAL 7 DAY) and NOW();

This will give you the temporary table you need. Then, you can preform your aggregation like this:

SELECT lead_create_date AS date, COUNT(*) AS numLeads
FROM myTable
GROUP BY lead_create_date;

And then outer join that to your temporary table which will fill in 0 values:

SELECT tmp.date, COALESCE(m.numLeads, 0) AS numLeads
FROM(
  SELECT a.Date
  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) a
    WHERE a.date BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND NOW()) tmp
LEFT JOIN(
  SELECT lead_create_date AS date, COUNT(*) AS numLeads
  FROM myTable 
  GROUP BY lead_create_date) m ON m.date = tmp.date
ORDER BY tmp.date;

It looks monstrous, but it works. Based on the comments in the other answer, it looks to work pretty well too.

Here is an SQL Fiddle example.

---

EDIT

Some clarification, the first query I gave was just to show how to create a temporary table with the last 7 dates. The second query shows how to preform aggregation on the whole table to get the counts for all dates in your table. The third query combines them together to only show the counts of the last seven days, which is the answer you are looking for.

Answer 2

Here you go the query is

select 
t1.Date,
coalesce(t2.Leads, 0) AS Leads
from
(
  select DATE_FORMAT(a.Date,'%Y-%m-%d') as Date
  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
  ) a
  where a.Date BETWEEN curdate() - interval 7 DAY AND curdate()
)t1
left join
(
  SELECT
  DATE(lead_create_date) AS `Date`,
  COUNT(*) AS `Leads`
  from  tab_leads
  WHERE
  lead_create_date >=  CURRENT_DATE - INTERVAL 6 DAY
  GROUP BY DATE(lead_create_date)
)t2
on t2.Date = t1.Date
group by t1.Date
order by t1.Date desc

The first part of the query is just to generate the dates for the given range and then use it as a left table and do a left join to your original query.

From the sample data you will get result as

+------------+-------+
| Date       | Leads |
+------------+-------+
| 2015-05-08 |     2 |
| 2015-05-07 |     1 |
| 2015-05-06 |     1 |
| 2015-05-05 |     1 |
| 2015-05-04 |     0 |
| 2015-05-03 |     2 |
| 2015-05-02 |     2 |
| 2015-05-01 |     0 |
+------------+-------+
8 rows in set (0.02 sec)

Answer 3

Not pretty but try this?

            SELECT tmp.dt, src.Leads
            FROM (
                SELECT CURRENT_DATE - INTERVAL 6 DAY as dt UNION
                    SELECT CURRENT_DATE - INTERVAL 5 DAY UNION
                    SELECT CURRENT_DATE - INTERVAL 4 DAY UNION
                    SELECT CURRENT_DATE - INTERVAL 3 DAY UNION
                    SELECT CURRENT_DATE - INTERVAL 2 DAY UNION
                    SELECT CURRENT_DATE - INTERVAL 1 DAY UNION
                    SELECT CURRENT_DATE) as tmp
                left join (
                    SELECT
                        DATE(lead_create_date ) AS `Date`,
                        COUNT(*) AS `Leads`
                    FROM
                        tab_leads
                    WHERE
                        lead_create_date >=  CURRENT_DATE - INTERVAL 6 DAY
                    GROUP BY


                        DATE(lead_create_date )
                ) as src on tmp.dt = src.`Date`