CODEWITHSUNDEEP
mysqlsqldatabasemariadb
Shri
Shri

Reputation: 751

SQL accumulates previous days results while getting number of records in past 24 hours

I've written this SQL query which returns the number of records created per hour in last 24 hours. The query works fine for the first day, But from the second day, it adds the number of records on the first day with the current day count.

Here's my current 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 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11 union all select 12 union all select 13 union all select 14 union all select 15 union all select 16 union all select 17 union all select 18 union all select 19 union all select 20 union all select 21 union all select 22 union all select 23
) h
left join events e 
    on e.timeStamp > now() - interval 24 hour
        and hour(e.timeStamp) = h.hr
group by h.hr

My DataSet:

+---------+----------+---------------------+---------------------+
| 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 |
+---------+----------+---------------------+---------------------+

Where am I going wrong?

This is my timeStamp format incase someone needs it: 2019-12-31 04:55:10

P.S: My sql does not support recursive CTEs so I had to use union 24 times.

Upvotes: 0

Views: 44

Answers (1)

zip
zip

Reputation: 4061

I do not have the test data but this cold be what you need:

 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 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11 union all select 12 union all select 13 union all select 14 union all select 15 union all select 16 union all select 17 union all select 18 union all select 19 union all select 20 union all select 21 union all select 22 union all select 23
) h
left join (select * from events e where e.timeStamp > now() - interval 24 hour) e
        on hour(e.timeStamp) = h.hr
group by h.hr

IF

select * from events e where e.timeStamp > now() - interval 24 hour

gives you just the records of the last 24 hours

IF you want events of the last day:

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 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11 union all select 12 union all select 13 union all select 14 union all select 15 union all select 16 union all select 17 union all select 18 union all select 19 union all select 20 union all select 21 union all select 22 union all select 23
) h
left join (select * from events e where e.timeStamp > cast(now()  as date)) e
        on hour(e.timeStamp) = h.hr
group by h.hr

Upvotes: 1

Related Questions