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!