Shri
Reputation: 751
SQL returns zero for number of records created in each day of past week
I'm writing this SQL query, where it counts the number of records added to a table each day in last 7 days. It's supposed to return x number of records for day 1, y for day 2 and so on. Here's my query;
select h.hr, count(e.eventID) as cnt
from ( select 0 hr union all
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6
) h left join
( select *
from events e
where e.timeStamp >= cast(now() as date)
) e
on day(e.timeStamp) = h.hr
group by h.hr
The problem is, when it is supposed to return number of records, it's returning value 0 for all 7 days. Where am I going wrong?
My Data Set:
+---------+----------+---------------------+---------------------+
| eventID | personID | timeStamp | dateModified |
+---------+----------+---------------------+---------------------+
| 1 | 8 | 2019-12-28 12:26:49 | 2019-12-28 12:26:50 |
| 2 | 0 | 2019-12-28 12:26:51 | 2019-12-28 12:26:52 |
| 3 | 0 | 2019-12-28 12:27:11 | 2019-12-28 12:27:12 |
| 4 | 0 | 2019-12-28 12:27:12 | 2019-12-28 12:27:12 |
| 5 | 0 | 2019-12-28 12:28:20 | 2019-12-28 12:28:20 |
| 6 | 0 | 2019-12-28 12:28:21 | 2019-12-28 12:28:21 |
| 7 | 0 | 0000-00-00 00:00:00 | 2019-12-28 12:28:21 |
| 8 | 2 | 2019-12-28 12:30:02 | 2019-12-28 12:30:02 |
| 9 | 0 | 2019-12-28 12:30:03 | 2019-12-28 12:30:03 |
| 10 | 1 | 2019-12-30 05:38:02 | 2019-12-30 05:38:01 |
| 11 | 0 | 2019-12-30 05:38:05 | 2019-12-30 05:38:03 |
| 12 | 0 | 2019-12-30 05:41:42 | 2019-12-30 05:41:41 |
| 13 | 1 | 2019-12-30 05:41:41 | 2019-12-30 05:41:41 |
| 14 | 1 | 2019-12-30 05:43:11 | 2019-12-30 05:43:11 |
| 15 | 0 | 2019-12-30 05:43:13 | 2019-12-30 05:43:11 |
| 16 | 8 | 2019-12-30 05:44:08 | 2019-12-30 05:44:08 |
| 17 | 0 | 2019-12-30 05:44:10 | 2019-12-30 05:44:08 |
| 18 | 1 | 2019-12-30 05:48:06 | 2019-12-30 05:48:06 |
| 19 | 0 | 2019-12-30 05:48:08 | 2019-12-30 05:48:07 |
| 20 | 1 | 2019-12-30 06:09:58 | 2019-12-30 06:09:57 |
| 21 | 0 | 2019-12-30 06:10:00 | 2019-12-30 06:09:58 |
| 22 | 0 | 2019-12-30 06:11:22 | 2019-12-30 06:11:20 |
| 23 | 1 | 2019-12-30 06:11:20 | 2019-12-30 06:11:20 |
| 24 | 1 | 2019-12-30 06:13:30 | 2019-12-30 06:13:30 |
| 25 | 0 | 2019-12-30 06:13:32 | 2019-12-30 06:13:30 |
| 26 | 0 | 0000-00-00 00:00:00 | 2019-12-30 06:13:42 |
| 27 | 0 | 0000-00-00 00:00:00 | 2019-12-30 06:14:00 |
| 28 | 1 | 2019-12-30 06:14:55 | 2019-12-30 06:14:54 |
| 29 | 0 | 2019-12-30 06:14:57 | 2019-12-30 06:14:55 |
| 30 | 0 | 0000-00-00 00:00:00 | 2019-12-30 06:14:59 |
| 31 | 8 | 2019-12-30 06:16:22 | 2019-12-30 06:16:22 |
| 32 | 0 | 2019-12-30 06:16:24 | 2019-12-30 06:16:22 |
| 33 | 0 | 0000-00-00 00:00:00 | 2019-12-30 06:16:27 |
| 34 | 8 | 2019-12-30 06:17:56 | 2019-12-30 06:17:56 |
| 35 | 0 | 2019-12-30 06:17:58 | 2019-12-30 06:17:56 |
| 36 | 1 | 2019-12-30 06:18:32 | 2019-12-30 06:18:31 |
| 37 | 0 | 2019-12-30 06:18:33 | 2019-12-30 06:18:31 |
| 38 | 0 | 0000-00-00 00:00:00 | 2019-12-30 06:18:37 |
| 39 | 8 | 2019-12-30 06:21:23 | 2019-12-30 06:21:23 |
| 40 | 0 | 2019-12-30 06:21:25 | 2019-12-30 06:21:23 |
| 41 | 0 | 2019-12-30 06:21:33 | 2019-12-30 06:21:32 |
| 42 | 0 | 2019-12-30 06:21:34 | 2019-12-30 06:21:32 |
| 43 | 1 | 2019-12-30 06:39:58 | 2019-12-30 06:39:57 |
| 44 | 0 | 2019-12-30 06:40:00 | 2019-12-30 06:39:59 |
| 45 | 1 | 2019-12-30 06:40:29 | 2019-12-30 06:40:29 |
| 46 | 0 | 2019-12-30 06:40:31 | 2019-12-30 06:40:29 |
| 47 | 1 | 2019-12-30 06:42:06 | 2019-12-30 06:42:05 |
| 48 | 0 | 2019-12-30 06:42:07 | 2019-12-30 06:42:05 |
| 49 | 1 | 2019-12-30 06:44:21 | 2019-12-30 06:44:20 |
| 50 | 0 | 2019-12-30 06:44:22 | 2019-12-30 06:44:21 |
| 51 | 1 | 2019-12-30 06:45:35 | 2019-12-30 06:45:34 |
| 52 | 0 | 2019-12-30 06:45:36 | 2019-12-30 06:45:34 |
| 53 | 1 | 2019-12-30 06:46:27 | 2019-12-30 06:46:27 |
| 54 | 0 | 2019-12-30 06:46:28 | 2019-12-30 06:46:27 |
| 55 | 1 | 2019-12-30 06:50:40 | 2019-12-30 06:50:39 |
| 56 | 0 | 2019-12-30 06:50:41 | 2019-12-30 06:50:39 |
| 57 | 8 | 2019-12-30 08:10:52 | 2019-12-30 08:10:57 |
| 58 | 0 | 2019-12-30 08:10:59 | 2019-12-30 08:10:58 |
| 59 | 1 | 2019-12-30 09:47:06 | 2019-12-30 09:47:07 |
| 60 | 0 | 2019-12-30 09:47:08 | 2019-12-30 09:47:07 |
| 61 | 1 | 2019-12-30 11:43:17 | 2019-12-30 11:43:17 |
| 62 | 0 | 2019-12-30 11:43:18 | 2019-12-30 11:43:18 |
| 63 | 1 | 2019-12-30 11:43:35 | 2019-12-30 11:43:35 |
| 64 | 0 | 2019-12-30 11:43:36 | 2019-12-30 11:43:35 |
| 65 | 1 | 2019-12-30 11:44:05 | 2019-12-30 11:44:05 |
| 66 | 0 | 2019-12-30 11:44:06 | 2019-12-30 11:44:06 |
| 67 | 1 | 2019-12-30 12:26:47 | 2019-12-30 12:26:47 |
| 68 | 0 | 2019-12-30 12:26:49 | 2019-12-30 12:26:48 |
| 69 | 0 | 2019-12-30 12:40:34 | 2019-12-30 12:40:33 |
| 70 | 1 | 2019-12-30 12:40:33 | 2019-12-30 12:40:34 |
| 71 | 8 | 2019-12-31 04:52:29 | 2019-12-31 04:52:28 |
| 72 | 0 | 2019-12-31 04:52:31 | 2019-12-31 04:52:29 |
| 73 | 0 | 2019-12-31 04:53:50 | 2019-12-31 04:53:47 |
| 74 | 8 | 2019-12-31 04:53:48 | 2019-12-31 04:53:47 |
| 75 | 8 | 2019-12-31 04:54:14 | 2019-12-31 04:54:13 |
| 76 | 0 | 2019-12-31 04:54:16 | 2019-12-31 04:54:14 |
| 77 | 8 | 2019-12-31 04:54:40 | 2019-12-31 04:54:38 |
| 78 | 0 | 2019-12-31 04:54:41 | 2019-12-31 04:54:39 |
| 79 | 0 | 2019-12-31 04:55:11 | 2019-12-31 04:55:09 |
| 80 | 8 | 2019-12-31 04:55:10 | 2019-12-31 04:55:10 |
+---------+----------+---------------------+---------------------+
Upvotes: 1
Views: 77
Answers (2)
Levent Üncü
Reputation: 335
You should use
DAYOFWEEK()
function so your query would be like
SELECT DAYOFWEEK(e.timeStamp),
count(EventId) from events where
e.timeStamp >= DATE(CURDATE) - INTERVAL 7 DAY
group by DAYOFWEEK(e.timeStamp)
ps:I can't try it now, but it should be working.
Upvotes: 0
GMB
Reputation: 222562
You are not joining correctly the numbers table with your dataset.
I think that you want:
select d.dy, count(e.eventID) as cnt
from ( select 0 dy union all
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6
) d
left join events e
on e.timestamp >= current_date - interval d.dy day
and e.timestamp < current_date - interval (d.dy - 1) day
group by d.dy
Note: I renamed the subquery to d(dy) so its purpose is clearer.
Upvotes: 2
Related Questions
- Laravel: How to get count of all records created within in current week as of yesterday
- How to display the days when there are no records in MariaDB?
- Count all records per day in a specific month
- Get report of last week of all days including days with 0 count
- SQL accumulates previous days results while getting number of records in past 24 hours
- Records count per day, including 0 values
- Get record count of each day of last week. if no record found in particular date then i want 0 as record
- MySQL count records for the past 7 days grouped by and with zero records
- Count number of records per week giving massive number of NULL
- How to find all new rows in a mysql table that were added in the last week (with no date column in table)