group by in Nested Aggregates

Use group by to nest a vector aggregate inside a scalar aggregate.

For example, to find the average price of all types of books using a nonnested 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.