Question

Produce a list of facilities along with their total revenue. The output table should consist of facility name and revenue, sorted by revenue. Remember that there's a different cost for guests and members!
Schema reminder
DB schema

Expected Results

name revenue
Table Tennis 180
Snooker Table 240
Pool Table 270
Badminton Court 1906.5
Squash Court 13468.0
Tennis Court 1 13860
Tennis Court 2 14310
Massage Room 2 15810
Massage Room 1 72540

Your Answer Hint Help Save Run Query


              

Answers and Discussion Show

select facs.name, sum(slots * case
			when memid = 0 then facs.guestcost
			else facs.membercost
		end) as revenue
	from cd.bookings bks
	inner join cd.facilities facs
		on bks.facid = facs.facid
	group by facs.name
order by revenue;          
The only real complexity in this query is that guests (member ID 0) have a different cost to everyone else. We use a case statement to produce the cost for each session, and then sum each of those sessions, grouped by facility.
Remember the CASE statement!

Keyboard shortcuts:


Other hints: