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 ...

Get Joe Celko's SQL for Smarties, 3rd Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.