Correlated Subqueries in a HAVING Clause
Using sub queries in the HAVING clause is little different than using a sub query in any other part of the clause. The one thing that may through you is the WHERE clause inside the sub query… it acts as a JOIN criteria, so you might not get the placement right, until you’ve done it a few times.
The following example finds the product models for which the maximum list price is more than twice the average for the model.
USE AdventureWorks GO SELECT p1.ProductModelID FROM Production.Product p1 GROUP BY p1.ProductModelID HAVING MAX(p1.ListPrice) >= ALL (SELECT 2 * AVG(p2.ListPrice) FROM Production.Product p2 WHERE p1.ProductModelID = p2.ProductModelID ) GO
In this case, the sub query is evaluated once for each group defined in the outer query, that is, once for each model of product.
Again, there’s little difference in using a sub query in the HAVING clause. If you have any questions, send them in, I’m here to help!