List the total slots booked per facility in a given month
Question
Produce a list of the total number of slots booked per facility in the month of September 2012. Produce an output table consisting of facility id and slots, sorted by the number of slots.
Expected Results
| facid | Total Slots |
|---|---|
| 5 | 122 |
| 3 | 422 |
| 7 | 426 |
| 8 | 471 |
| 6 | 540 |
| 2 | 570 |
| 1 | 588 |
| 0 | 591 |
| 4 | 648 |
Your Answer
Your Results
Loading database...
Answers and Discussion
select facid, sum(slots) as "Total Slots"
from cd.bookings
where
starttime >= '2012-09-01'
and starttime < '2012-10-01'
group by facid
order by sum(slots);This is only a minor alteration of our previous example. Remember that aggregation happens after the WHERE clause is evaluated: we thus use the WHERE to restrict the data we aggregate over, and our aggregation only sees data from a single month.