My church has a Sunday school program taught by adult volunteers. The children and the volunteers both “check in” to the classroom when they arrive. There is a distinction in the system between “regular” check-ins, and “volunteer” check-ins.
I have three tables that I’m working with for this query: _locations, _check_ins, and _event_times. Here is some sample data for each:
_locations:
| _id | _level |
|---|---|
| 1 | Nursery |
| 2 | Preschool |
| 3 | Elementary |
_check_ins:
| _id | _location_id | _event_time_id | _kind |
|---|---|---|---|
| 1 | 1 | 1 | Regular |
| 2 | 1 | 1 | Regular |
| 3 | 1 | 1 | Volunteer |
| 4 | 2 | 1 | Regular |
| 5 | 2 | 1 | Regular |
| 6 | 2 | 1 | Volunteer |
| 7 | 3 | 2 | Regular |
| 8 | 3 | 2 | Regular |
| 9 | 3 | 2 | Volunteer |
_event_times:
| _id | _time |
|---|---|
| 1 | 2024-06-16 09:00:00 |
| 2 | 2024-06-16 11:00:00 |
So far I’ve got a query that joins them together like this:
SELECT e._time, l._level, count(c._id) service_count
FROM (_locations l
JOIN _check_ins c ON l._id = c._location_id
JOIN _event_times e ON c._event_time_id = e._id)
GROUP BY e._time, l._level
This counts the TOTAL number of check-ins for each room, for each service. But what I would like to do is take it a step further and have two “count” columns. One should have the number of “Regular” check-ins, and the other should have the number of “Volunteer” check-ins. Still per room, per service. How can I achieve this?
Additionally, my query as it stands only shows rows for rooms and services that actually had any check-ins. If a room were to have no students for a particular service, how can I still include that row, and just have the count columns read 0? I think it has something to do with the types of the joins, but I can’t figure it out.