Expression subqueries can be correlated subqueries.
select s1.ord_num, s1.title_id, s1.qty from salesdetail s1 where title_id like "PS%" and s1.qty < (select avg(s2.qty) from salesdetail s2 where s2.title_id = s1.title_id)
ord_num title_id qty ------------------ -------- --- 91-A-7 PS3333 90 91-A-7 PS2106 30 55-V-7 PS2106 31 AX-532-FED-452-2Z7 PS7777 125 BA71224 PS7777 200 NB-3.142 PS2091 200 NB-3.142 PS7777 250 NB-3.142 PS3333 345 ZD-123-DFG-752-9G8 PS3333 750 91-A-7 PS7777 180 356921 PS3333 200
The outer query selects the rows of the sales table (or 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, Transact-SQL evaluates the subquery and includes 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. To find titles of books that have prices higher than average for books of the same type, the query is:
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
For each possible value of t1, Transact-SQL evaluates the subquery and includes the row in the results if the price value of that row is greater than the calculated average. You need not group explicitly by type, because the rows for which the average price is calculated are restricted by the where clause in the subquery.