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 vote200 views2 answers
Sql server set default value equal to auto increment
SQL
3 votes249 views1 answer
Mysql cli connect with password
SQL
2 votes165 views4 answers
Mysql change database character set to utf8mb4
SQL
0 vote277 views1 answer
Radius search with point data in mysql
SQL
0 vote210 views1 answer
Code to move ietms from one table to another myswl
SQL
2 votes206 views2 answers
Mysql bidirectional composite primary key
SQL
1 vote230 views1 answer
Psql human readable
SQL
1 vote219 views2 answers
Django.core.exceptions.ImproperlyConfigured: mysqlclient 1.4.0 or newer is required; you have 0.10.1.
SQL
0 vote204 views1 answer
Adonisjs ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client
SQL
2 votes211 views1 answer
Get hour from date sql
SQL