Question

Produce a list of the total number of slots booked per facility per month in the year of 2012. Produce an output table consisting of facility id and slots, sorted by the id and month.
Schema reminder
DB schema

Expected Results

facid month Total Slots
0 7 270
0 8 459
0 9 591
1 7 207
1 8 483
1 9 588
2 7 180
2 8 459
2 9 570
3 7 104
3 8 304
3 9 422
4 7 264
4 8 492
4 9 648
5 7 24
5 8 82
5 9 122
6 7 164
6 8 400
6 9 540
7 7 156
7 8 326
7 9 426
8 7 117
8 8 322
8 9 471

Your Answer Hint Help Save Run Query

 

Answers and Discussion Show

select facid, extract(month from starttime) as month, sum(slots) as "Total Slots"
	from cd.bookings
	where
		starttime >= '2012-01-01'
		and starttime < '2013-01-01'
	group by facid, month
order by facid, month;          

The main piece of new functionality in this question is the EXTRACT function. EXTRACT allows you to get individual components of a timestamp, like day, month, year, etc. We group by the output of this function to provide per-month values. An alternative, if we needed to distinguish between the same month in different years, is to make use of the DATE_TRUNC function, which truncates a date to a given granularity.

It's also worth noting that this is the first time we've truly made use of the ability to group by more than one column.

Take a look at the EXTRACT function.

Keyboard shortcuts:


Other hints: