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')
;

Check this out http://stackoverflow.com/questions/23300303/mysql-single-table-select-last-7-days-and-include-empty-rows/23301236#23301236 — Abhik Chakraborty, May 08 '15 at 12:45
You're trying to have the system return data when no data is present. SQL doesn't make up data when no data is present. As @AbhikChakraborty points out in Gordon's or Abhik's answers,, you need to derive a table of dates for the 7 days and use an OUTER join to tab_leads based on those dates which will keep the 7 days of interest and return the zero count you're looking for. — xQbert, May 08 '15 at 12:59
@xQbert well I have also provided a solution to generate the date list dynamically for the given range and then left join to the original table and it works pretty well. Though the query is a bit long but once you go through it its easy. — Abhik Chakraborty, May 08 '15 at 13:02
@Tikky if you create a fiddle with some sample data I can write the query as I provided in my link above. — Abhik Chakraborty, May 08 '15 at 13:14
@xQbert: "SQL doesn't make up data when no data is present." OK, I would not say that this needs to be done in direct Sql. Mabye it will be easier to modify an array of elements in PHP? — Tikky, May 08 '15 at 13:25
Absolutely True. It can be done in SQL, but you may find the PHP [array_merge](http://php.net/manual/en/function.array-merge.php) to work better if you have an array of the desired dates to merge into from the array of values from SQL. — xQbert, May 08 '15 at 13:30
@Abhik Chakraborty: Just trying to create SQLfiddle withous success. I've added my data to the problem description above — Tikky, May 08 '15 at 13:46

score 2 · Answer 1 · answered May 08 '15 at 13:12

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`

Really don't need the sub query on tab_leads. A simple outer join would suffice. — xQbert, May 08 '15 at 13:32
This works fine for me, however if I need for example in other query get a sum from 30 days I need to modify the whole qyery. Is there a way to modify this query to have a number of days as a variable, I mean digit - so I will be able to change it in PHP — Tikky, May 11 '15 at 09:49
Then I would recommend processing the results of your original query with PHP instead. MySQL does not have table value functions which would be handy in this kind of situation. But alas, it's free to use so we have to make do. — blubear, May 12 '15 at 04:08

score 2 · Accepted Answer · answered May 08 '15 at 14:07

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)

answered May 08 '15 at 14:07

Abhik Chakraborty

44,654
6
52
63

While it's just a matter of syntax, since you're outer joining to the table with the last 7 dates, you don't really need the `where lead_crate_date >= CURRENT_DATE - INTERVAL 6 DAY` right? If they don't meet that criteria, they just don't be joined. – AdamMc331 May 08 '15 at 15:16
1

Yes right, however with large data set from the joining table you definitely would not like to select everything from that table instead filter out data with an indexed date column. – Abhik Chakraborty May 08 '15 at 16:28
I've tried your SQL Query in my real database and: - it was done without errors - I've received 8 sums instead of 7 - all of the sums are "0" Could you please take a look once again, maybe something needs to be fixed? – Tikky May 11 '15 at 07:20
Here is the fiddle http://www.sqlfiddle.com/#!9/630fd/2 the first one is your query then the one I added in the answer, could you please let me know what would be the expected result ? – Abhik Chakraborty May 11 '15 at 07:24
Thank you. In your fiddle, line: `where a.Date BETWEEN curdate() - interval 7 DAY AND curdate()` I've changed 7 to 6, and now it gaves me 7 sums, which is correct. So seems to be ok, let me check it in my real database. – Tikky May 11 '15 at 07:42
AH i see I have added 7 in the interval for generating the dynamic dates, which should be 6. – Abhik Chakraborty May 11 '15 at 07:50
It works fine - thank you. In my CodeIgniter application where I want to use it I am only having a trouble in escaping fragment `'%Y-%m-%d'` – Tikky May 11 '15 at 08:27

score 1 · Answer 3 · edited May 23 '17 at 11:58

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.

This is really interesting however the first query is searching all database instead of last 7 days and in my real database it generated me a huge table. Please take a look at modified fiddle where I've added some dates from the past: [link](http://sqlfiddle.com/#!9/fd8710/1). BTW it is possible to run this as a one query instead of two? — Tikky, May 11 '15 at 07:36
@Tikky yes, the second query is the answer, the first query was just to demonstrate how to get all of the counts. The second query gets those counts for the specified date range. The query [works](http://sqlfiddle.com/#!9/5ff87/3) for me as long as there are rows in the last 7 days. I apologize for the confusion, and the delayed response. I was on vacation. I have edited my answer as well. — AdamMc331, May 15 '15 at 13:14
Thank you for provided informations, this is really useful for me, and your method works fine :) — Tikky, May 18 '15 at 07:07

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

3 Answers3