CODEWITHSUNDEEP
mysql
Liam Hendricks
Liam Hendricks

Reputation: 43

MYSQL - How to get row count for each day for the last 7 days and include days that don't exist in table

I want to construct a query that returns a count of unique rows per day for the last 7 days, and days that are not in the table should then be filled in with a zero count

Example Data

+----+----------------+---------------------+
| PK |   smsMessage   |       t_stamp       |
+----+----------------+---------------------+
| 0  | 'Alarm active' | 2022-09-19 22:23:56 |
| 1  | 'Alarm active' | 2022-09-19 22:23:41 |
| 2  | 'Alarm active' | 2022-09-19 22:23:42 |
| 3  | 'Alarm active' | 2022-09-19 22:23:56 |
| 4  | 'Alarm active' | 2022-09-19 22:23:25 |
| 5  | 'Alarm active' | 2022-09-20 22:23:57 |
| 6  | 'Alarm active' | 2022-09-20 22:23:40 | 
| 7  | 'Alarm active' | 2022-09-20 22:23:23 |
| 8  | 'Alarm active' | 2022-09-20 22:23:55 |
| 9  | 'Alarm active' | 2022-09-21 22:29:38 |
| 10 | 'Alarm active' | 2022-09-21 21:31:59 |
+----+----------------+---------------------+

Example Return

+-------+------------+
| count |   date     |
+-------+------------+
|   0   | 2020-02-16 |
|   0   | 2020-02-17 |
|   0   | 2020-02-18 |
|   5   | 2020-02-19 |
|   4   | 2020-02-20 |
|   2   | 2020-02-21 |
+-------+------------+

Upvotes: 1

Views: 61

Answers (1)

Ozan Sen
Ozan Sen

Reputation: 2615

Hello you can achieve your result using recursive cte. Here is the code:

WITH RECURSIVE dates(date) AS 
(SELECT CAST(MAX(t_stamp) AS date) FROM Messaging
 UNION ALL
 SELECT (date - INTERVAL 1 DAY) FROM dates LIMIT 6
)
SELECT COALESCE(COUNT(CAST(t_stamp as date)),0) as count, dates.date
FROM dates LEFT JOIN Messaging ON dates.date = CAST(t_stamp AS date)
GROUP BY dates.date
ORDER BY dates.date;

If we test it, It returns:

Regal

Please note that your mysql version should be at least (8.0.19) or higher in order to use limit clause in recursive cte. Before this version, Limit is not supported in recursive cte's.

you can learn your mysql version by running the simple command:

SELECT version();

Version

Upvotes: 2

Related Questions