Chapter 3: Using Aggregates, Grouping, and Sorting

Using group by in nested aggregates

Another Transact-SQL extension allows you to nest a vector aggregate inside a scalar aggregate. For example, to find the average price of all types of books using a non-nested aggregate, enter:

select avg(price) 
from titles 
group by type

--------------- 
 NULL 
13.73 
11.49 
21.48 
13.50 
15.96 
 
(6 rows affected)

Nesting the average price inside the max function produces the highest average price of a group of books, grouped by type:

select max(avg(price)) 
from titles 
group by type

------------- 
        21.48 
 
(1 row affected)

By definition, the group by clause applies to the innermost aggregate—in this case, avg.

View this book as PDF