Joe Celko's SQL for Smarties, 3rd Edition

20.3. Multiple Aggregation Levels

The rule in SQL is that you cannot nest aggregate functions, such as

SELECT department, MIN(COUNT(items))  -- illegal syntax!
  FROM Foobar
 GROUP BY department;

The usual intent of this is to get multiple levels of aggregation; this example probably wanted the smallest count of items within each department. But this makes no sense, because a department (i.e., a group) can have only one count, one minimum, one maximum, one average, and so forth for any expression. The nature of descriptive statistics is that they reduce a group characteristic to a scalar value.

20.3.1. Grouped VIEWs for Multiple Aggregation Levels

Business reports are usually based on a hierarchy of nested levels of aggregation. This type of report ...