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.