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

Viewed 650

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?

2 Answers
Related