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

2 votes305 views1 answer
How to create sql database
SQL
0 vote180 views1 answer
Mysql date time string format for marshmellow field schema
SQL
1 vote185 views2 answers
Logical operators in sql
SQL
1 vote290 views1 answer
Mysql breakline on string
SQL
0 vote178 views1 answer
Caused by: java.lang.RuntimeException: Unable to obtain credentials to communicate with the Cloud SQL API
SQL
0 vote180 views1 answer
GROUP BY postgres
SQL
1 vote172 views1 answer
Postgresql substring last
SQL
1 vote179 views1 answer
Host 127.0 0.1 is not allowed to connect to this mysql server
SQL
0 vote159 views1 answer
Mysql select statement after index
SQL
0 vote160 views2 answers
ON DUPLICATE KEY UPDATE for postgres
SQL