Correlated subqueries in a having clause

Quantified predicate subqueries can be correlated subqueries.

This example of a correlated subquery in the having clause of an outer query finds the types of books in which the maximum advance is more than twice the average within a given group:

select t1.type 
from titles t1 
group by t1.type 
having max(t1.advance) >= any 
   (select 2 * avg(t2.advance) 
   from titles t2 
   where t1.type = t2.type)

type 
---------- 
mod_cook

The subquery above is evaluated once for each group that is defined in the outer query, that is, once for each type of book.