Select rows with maximum value from select result

Question

I want to select rows with max vallues from the following select, but result table name is wrong in subquery (select max(result.Sum) from result):

select *
from 
(select sum(Rooms.n_seats) as 'Sum', DepKinds.title
from Rooms join Departments on Rooms.department = Departments.id
join DepKinds on Departments.kind = DepKinds.id group by DepKinds.title) result where result.Sum = (select max(result.Sum) from result);

So how to select maximum sum and title for those sums?

Answer 1

It is not possible to use the result alias behind FROM in the subquery. Try to use HAVING.

select sum(Rooms.n_seats) as 'Sum', DepKinds.title
from Rooms 
join Departments on Rooms.department = Departments.id
join DepKinds on Departments.kind = DepKinds.id 
group by DepKinds.title
having sum(Rooms.n_seats) >= all
(
   select sum(Rooms.n_seats) 
   from Rooms 
   join Departments on Rooms.department = Departments.id
   join DepKinds on Departments.kind = DepKinds.id 
   group by DepKinds.title   
)

This will return all DepKinds.title having a maximum number of seats.