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

Viewed 32

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 |
+-------+------------+
0 Answers
Related