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

1 vote240 views1 answer
Oracle nvl2
SQL
5 votes188 views1 answer
Alter table default value mysql
SQL
0 vote267 views1 answer
Sql create and grant user access to database
SQL
0 vote220 views1 answer
Horizontal join sqlite
SQL
1 vote214 views1 answer
Autocommit mysql
SQL
1 vote214 views2 answers
Mysql trim whitespace
SQL
0 vote220 views1 answer
Oracle procedure chain step
SQL
1 vote232 views1 answer
Incompatible sql_mode=only_full_group_by
SQL
1 vote226 views1 answer
Sql find column name like
SQL
2 votes223 views1 answer
Sql server find table name
SQL