Skip to content

Correlated Subqueries in a HAVING Clause

2008 June 30
by Shannon Lowder

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
FROM Production.Product p1
HAVING MAX(p1.ListPrice) >= ALL
		2 * AVG(p2.ListPrice)
	 FROM Production.Product p2
	 WHERE p1.ProductModelID = p2.ProductModelID

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!

No comments yet

Leave a Reply

Note: You can use basic XHTML in your comments. Your email address will never be published.

Subscribe to this comment feed via RSS