First make a sub-query or CTE that correctly parse out the physical hour, then run the GROUP BY on that query.
If you only want counts per hour, we only need the date and hour as fields in the CTE, which is rather trivial - I did this quick Example:
```
CREATE TABLE trafficdata (
EventID TEXT NOT NULL UNIQUE,
EventDate TEXT,
EventTime TEXT,
EventCode TEXT,
EventDescription TEXT,
EventAddress TEXT
);
INSERT INTO trafficdata (EventID,EventDate,EventTime,EventCode,EventDescription,EventAddress) VALUES
('LLV210400045327','4/10/2021', '10:28:17 PM','401B', 'ACCIDENT (WITH INJURY)','5075 E WASHINGTON AVE')
,('LLV210400045227','4/10/2021', '10:02:08 PM','401', 'ACCIDENT (TRAFFIC)','3667 S LAS VEGAS BLVD')
,('LLV210400045138','4/10/2021', '9:37:40 PM', '401C', 'ACCIDENT (PRIVATE PROPERTY)','7614 WESTCLIFF DR')
,('LLV210400045066','4/10/2021', '9:21:49 PM', '401A', 'HIT AND RUN','E TROPICANA AVE / KOVAL LN')
,('LLV210400045017','4/10/2021', '9:09:51 PM', '401', 'ACCIDENT (TRAFFIC)','5229 DUNEVILLE ST')
,('LLV210400044913','4/10/2021', '8:47:03 PM', '401B', 'ACCIDENT (WITH INJURY)','W DESERT INN RD / S RAINBOW BLVD')
,('LLV210400044827','4/10/2021', '8:26:13 PM', '401A', 'HIT AND RUN','N NELLIS BLVD / E CAREY AVE')
;
-- Check to see that we have added the data correctly:
SELECT EventDate, EventTime
FROM trafficdata;
-- EventDate |EventTime
-- -----------|-------------
-- 4/10/2021 |10:28:17 PM
-- 4/10/2021 |10:02:08 PM
-- 4/10/2021 |9:37:40 PM
-- 4/10/2021 |9:21:49 PM
-- 4/10/2021 |9:09:51 PM
-- 4/10/2021 |8:47:03 PM
-- 4/10/2021 |8:26:13 PM
-- Make the CTE and GROUP BY the hour to see counts per hour:
WITH HR(EventDate, EventTime, EventHour) AS (
SELECT EventDate, EventTime,
CAST(substr(EventTime,0,instr(EventTime,':')) AS INT)+
CASE WHEN EventTime LIKE '%PM' THEN 12 ELSE 0 END
FROM trafficdata
)
SELECT EventDate, EventHour, COUNT(*) AS AccidentsInHour
FROM HR
GROUP BY EventDate, EventHour
;
-- | |Accident-
-- EventDate | EventHour | sInHour
-- -----------|------------|---------
-- 4/10/2021 | 20 | 2
-- 4/10/2021 | 21 | 3
-- 4/10/2021 | 22 | 2
```
Hope that helps!
PS: This data sadly cannot be sorted because of those bad date & time formats. You should really use proper ISO8601 format (or at least a sub-format of ISO8601 that SQLite can work with - i.e. YYYY-MM-DD HH:NN:SS).