SQLite Forum

SQL query help for between times and grouping times
Login
I THINK everything is right with this CTE query.

Example data here: <https://termbin.com/9y2s>


Now for the results:
```
┌───────────┬───────────┬─────────────────┐
│ EventDate │ EventHour │ AccidentsInHour │
├───────────┼───────────┼─────────────────┤
│ 4/16/2021 │ 2         │ 1               │
│ 4/16/2021 │ 3         │ 2               │
│ 4/16/2021 │ 5         │ 4               │
│ 4/16/2021 │ 6         │ 2               │
│ 4/16/2021 │ 7         │ 4               │
│ 4/16/2021 │ 8         │ 7               │
│ 4/16/2021 │ 9         │ 2               │
│ 4/16/2021 │ 10        │ 5               │
│ 4/16/2021 │ 11        │ 5               │
│ 4/16/2021 │ 12        │ 1               │
│ 4/16/2021 │ 13        │ 14              │
│ 4/16/2021 │ 14        │ 5               │
│ 4/16/2021 │ 15        │ 11              │
│ 4/16/2021 │ 16        │ 12              │
│ 4/16/2021 │ 17        │ 5               │
│ 4/16/2021 │ 18        │ 8               │
│ 4/16/2021 │ 19        │ 4               │
│ 4/16/2021 │ 20        │ 6               │
│ 4/16/2021 │ 21        │ 5               │
│ 4/16/2021 │ 22        │ 6               │
│ 4/16/2021 │ 23        │ 4               │
│ 4/16/2021 │ 24        │ 12              │
└───────────┴───────────┴─────────────────┘
```

24 seems to represent 12nooon. Can you explain which part of the substitution does that?

These CTE things are really, really cool and powerful!