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 vote186 views3 answers
Create and attach user to a postgresql database
SQL
2 votes184 views2 answers
Mysql user access to database
SQL
1 vote247 views1 answer
Postgresql float 2 decimal places
SQL
-1 vote237 views3 answers
MAKE TABLE FIT in oracle sql
SQL
2 votes197 views1 answer
Oracle percentage
SQL
0 vote198 views1 answer
Mysql get first 2 characters of string
SQL
4 votes319 views1 answer
What are the data types
SQL
1 vote203 views1 answer
Sqlite update from another table
SQL
2 votes233 views2 answers
Sql query to copy data from one column to another
SQL
0 vote195 views1 answer
Postgresql CREATE EXTENSION pgcrypto
SQL