how to group by a date range in MySQL and not skip dates with zero items

Question

well, this might be easy but I can't seem to find any solution

I have this working query

SELECT 
    count(*), TIMESTAMPDIFF(DAY, buydate, NOW()) daydif
FROM
    order_item
WHERE
    buydate > NOW() - INTERVAL 4 DAY
GROUP BY daydif
ORDER BY daydif ASC

but the result is skipping the days with zero item

+----------+--------+
| count(*) | daydif |
+==========+========+
| 5        | 0      |
+----------+--------+
| 9        | 1      |
+----------+--------+
| 2        | 3      |
+----------+--------+

I want to get this result

+----------+--------+
| count(*) | daydif |
+==========+========+
| 5        | 0      |
+----------+--------+
| 9        | 1      |
+----------+--------+
| 0        | 2      |  //I want this row in the returned results
+----------+--------+
| 2        | 3      |
+----------+--------+

Update:
From the answers and further search it seems I'm forced to create a helper table and join with it. It's so frustrating that I can't find a sequence producing function like that of MariaDB as mentioned in the comments. Any hint for a more elegant way?

You can create a calendar table and left join it with your `order_item` table using the date. [Here](http://stackoverflow.com/questions/35893704/count-active-users-based-on-logins-last-90-days/35899004#35899004) you will find how to create a calendar table. — Paul Spiegel, Mar 10 '16 at 18:12
In this case there is no need for a calendar table, it's enough to have 5 numbers. — Shadow, Mar 10 '16 at 18:15
@PaulSpiegel, wow that's really clever! wouldn't creating that table in a shorter range on the fly be a better option? — azerafati, Mar 10 '16 at 18:31
@Shadow, this is just a very simplified version of my actual code. I need to keep it dynamic and ranging to even some months — azerafati, Mar 10 '16 at 18:33
@Bludream, if you want to be flexible, you better create a helper table (sequence numbers not dates in this case) like Shadow sugested. — Paul Spiegel, Mar 10 '16 at 18:35
@Bludream: still it is simpler to have a list of numbers in a table, than having a whole calendar table. Unless you need the calendar table for other reasons as well ouside of this query. But this specific query would work better with a numbers table. — Shadow, Mar 10 '16 at 18:35
BTW: If you use MariaDB, you might want to have a look at the [sequence plugin](https://mariadb.com/kb/en/mariadb/sequence/). Just tested `SELECT seq as daydif FROM seq_0_to_5_step_1` - works fine. — Paul Spiegel, Mar 10 '16 at 18:58

Shadow · Answer 1 · 2016-03-11T14:23:02.673

1

Probably you do not have any items, where the difference is 2 days. SQL can't create something, that is not there.

What you can do is to have a table with a single field of long list of numbers from 0 to up to whatever the difference can be (in this case 4), let's call it tbl_diff, and the field is diff. Left join tbl_diff on your existing query to fill in the gaps. Note, that I also changed the count() to count those records only, where buydate is set.

SELECT 
    count(buydate), TIMESTAMPDIFF(DAY, buydate, NOW()) daydif
FROM
    tbl_diff
LEFT JOIN
    order_item on tbl_diff.diff=TIMESTAMPDIFF(DAY, buydate, NOW())
WHERE
    diff<=4
GROUP BY daydif
ORDER BY daydif ASC

If you do not want to create a helper table, then you can operate with a series of unions because you only want 5 number (0 to 4):

SELECT 
    count(buydate), TIMESTAMPDIFF(DAY, buydate, NOW()) daydif
FROM
    (SELECT 0 as diff FROM dual
     UNION
     SELECT 1 FROM dual
     UNION
     SELECT 2 FROM dual
     UNION
     SELECT 3 FROM dual
     UNION
     SELECT 4 FROM dual) tbl_diff
LEFT JOIN
    order_item on tbl_diff.diff=TIMESTAMPDIFF(DAY, buydate, NOW())
GROUP BY daydif
ORDER BY daydif ASC

edited Mar 11 '16 at 14:23

answered Mar 10 '16 at 18:14

Shadow

33,525
10
51
64

1

`buydate > NOW() - INTERVAL 4 DAY` should be in the ON clause (IMHO) – Paul Spiegel Mar 10 '16 at 18:22
@PaulSpiegel I'm not sure if that would make any difference. Actually, with the join condition it is kind of redundant, since if the difference is greater than 4 days, then the join would drop the record. – Shadow Mar 10 '16 at 18:32
If daydif is 2 you will check `NULL > NOW() - INTERVAL 4 DAY` and so filter out that row. And that is OPs problem. – Paul Spiegel Mar 10 '16 at 18:44
@PaulSpiegel you are right. However, I rather changed the where clauses completely. In the 2nd case, there is not even a need for a where clause. – Shadow Mar 11 '16 at 14:23

score 0 · Accepted Answer · answered Mar 12 '16 at 16:18

And as suggested by Paul Spiegel I ended up using some helper tables, for future reference and better help here's the code to I used to generate the tables

create table `stat_helper_calendar` (
  `date` DATE NOT NULL,
  PRIMARY KEY (`date`)
)
  select date_add('2015-01-01', INTERVAL  t3.c*1000 + t2.c*100 + t1.c*10 + t0.c DAY) as `date`
  from
    (select 0 c 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) t0,
    (select 0 c 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) t1,
    (select 0 c 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) t2,
    (select 0 c 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) t3

And this one for integers

CREATE TABLE `stat_helper_num` (
  `num` INT NOT NULL,
  PRIMARY KEY (`num`)
) SELECT (t4.c * 10000 + t3.c * 1000 + t2.c * 100 + t1.c * 10 + t0.c) AS `num` FROM
  (SELECT 0 c 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) t0,
  (SELECT 0 c 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) t1,
  (SELECT 0 c 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) t2,
  (SELECT 0 c 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) t3,
  (SELECT 0 c 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) t4

how to group by a date range in MySQL and not skip dates with zero items

2 Answers2