Subquery aggregation

select name, revenue from (
	select facs.name, sum(case 
				when memid = 0 then slots * facs.guestcost
				else slots * membercost
			end) as revenue
		from cd.bookings bks
		inner join cd.facilities facs
			on bks.facid = facs.facid
		group by facs.name
	) as agg where revenue < 1000
order by revenue;

Source: pgexercises.com

Similar codes

0 vote149 views2 answers
How to check if sqlite table exists in nodejs
SQL
0 vote140 views1 answer
How to define a save method in ruby for sql databases
SQL
0 vote203 views1 answer
Show mysql account privilleges
SQL
0 vote163 views1 answer
SQL Server date literal
SQL
0 vote146 views1 answer
Write a PL/SQL to print even numbers upto 100.
SQL
0 vote156 views1 answer
Mysql update set sum
SQL
0 vote148 views1 answer
Pl/ sql change currency
SQL
13 votes138 views4 answers
GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
SQL
5 votes152 views2 answers
Mysql import gzip db
SQL
3 votes164 views1 answer
Where id is in list sql
SQL