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 vote374 views1 answer
Traccar mysql
SQL
1 vote340 views1 answer
Write an SQL query to print details of the Workers whose FIRST_NAME ends with ‘h’ and contains six alphabets.
SQL
0 vote334 views1 answer
Windows aggregate functions in postgresql
SQL
13 votes365 views4 answers
What is sql
SQL
4 votes369 views1 answer
What are the data types
SQL
0 vote330 views1 answer
Mysql update table from select on another table
SQL
0 vote378 views1 answer
How to convert external table to managed table in hive
SQL
0 vote367 views1 answer
Date 3 months from today sql
SQL
1 vote379 views1 answer
Insert pg result of select
SQL
1 vote337 views1 answer
Oracle schema size
SQL