To find sales where the quantity is less than the average quantity for sales of that title:
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)
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 the sales table (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 statement. This example finds all titles that have a price greater than the average for books of its type:
SELECT t1.type, t1.title
FROM titles t1
WHERE t1.price >
(SELECT AVG(t2.price)
FROM titles t2
WHERE t1.type = t2.type)
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.