Correlated Subqueries with Comparison Operators

Use a correlated subquery with a comparison operator to find sales where the quantity is less than the average quantity for sales of that product.

USE AdventureWorks2008R2;
GO
SELECT ProductID, OrderQty
FROM Sales.SalesOrderDetail s1
WHERE s1.OrderQty <
    (SELECT AVG (s2.OrderQty)
     FROM Sales.SalesOrderDetail s2
     WHERE s2.ProductID = s1.ProductID)

The outer query selects the rows of SalesOrderDetail (that is, of s1) one by one. The subquery calculates the average quantity for each sale being considered for selection in the outer query. For each possible value of s1, SQL Server evaluates the subquery and puts the record being considered in the results if the quantity is less than the calculated average.

Sometimes a correlated subquery mimics a GROUP BY clause. This example finds all products that have a price greater than the average for products of its subcategory:

USE AdventureWorks2008R2;
GO
SELECT p1.ProductSubcategoryID, p1.Name
FROM Production.Product p1
WHERE p1.ListPrice >
    (SELECT AVG (p2.ListPrice)
     FROM Production.Product p2
     WHERE p1.ProductSubcategoryID = p2.ProductSubcategoryID)

For each possible value of p1, SQL Server evaluates the subquery and includes the row in the results if the ListPrice value of that row is greater than the calculated average. It is not necessary to group by type explicitly, because the rows for which average price is calculated are restricted by the WHERE clause in the subquery.