SQLite Forum

SQL query help for between times and grouping times
Login
Hi,

Happy Friday to all you SQLite fans and friends. I'd like help on grouping my EventTime column by the hours.

schema:
```
CREATE TABLE trafficdata (
    EventID TEXT NOT NULL UNIQUE,
    EventDate TEXT,
    EventTime TEXT,
    EventCode TEXT,
    EventDescription TEXT,
    EventAddress TEXT
);
```

Sample records:
```
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
```

Sample plain English question: `How many events were there in the 8, 9, and 10 o'clock hours?`

```
8pm,2
9pm,3
10pm,2
```

This doesn't work:
```
SELECT EventTime,
       Count(EventTime) AS count
FROM   trafficdata
WHERE  EventTime between '8:%%:%% PM' and '10:%%:%% PM'
GROUP  BY EventTime;  
```

This doesn't either:
```
SELECT EventTime,
       Count(EventTime) AS count
FROM   trafficdata
WHERE  EventTime like '8:%%:%% PM'
GROUP  BY EventTime;
```

What am I doing wrong?