A correlated subquery can also be used in the HAVING clause of an outer query. This construction can be used to find the types of books for which the maximum advance is more than twice the average within a given group.
In this case, the subquery is evaluated once for each group defined in the outer query (once for each type of book).
USE pubs
SELECT t1.type
FROM titles t1
GROUP BY t1.type
HAVING MAX(t1.advance) >=ALL
(SELECT 2 * AVG(t2.advance)
FROM titles t2
WHERE t1.type = t2.type)
Here is the result set:
type
--------
mod_cook
(1 row(s) affected)