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 title.
USE pubs
SELECT s1.ord_num, s1.title_id, s1.qty
FROM sales s1
WHERE s1.qty <
(SELECT AVG(s2.qty)
FROM sales s2
WHERE s2.title_id = s1.title_id)
Here is the result set:
ord_num title_id qty
-------------------- -------- ------
6871 BU1032 5
722a PS2091 3
D4482 PS2091 10
N914008 PS2091 20
423LL922 MC3021 15
(5 row(s) affected)
The outer query selects the rows of sales (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, Microsoft® 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 titles that have a price greater than the average for books of its type.
USE pubs
SELECT t1.type, t1.title
FROM titles t1
WHERE t1.price >
(SELECT AVG(t2.price)
FROM titles t2
WHERE t1.type = t2.type)
Here is the result set:
type title
------------ -----------------------------------------------------------
business The Busy Executive's Database Guide
business Straight Talk About Computers
mod_cook Silicon Valley Gastronomic Treats
popular_comp But Is It User Friendly?
psychology Computer Phobic AND Non-Phobic Individuals: Behavior
Variations
psychology Prolonged Data Deprivation: Four Case Studies
trad_cook Onions, Leeks, and Garlic: Cooking Secrets of the
Mediterranean
(7 row(s) affected)
For each possible value of t1, SQL Server evaluates the subquery and includes the row in the results if the price 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.