Nested Aggregation: Computing Aggregates of Aggregates

1. What nested aggregation means

Nested aggregation, sometimes called a second order aggregate, computes a metric not directly from a table's individual rows, but from an already aggregated intermediate level. A classic example is a shop's average daily revenue. To compute that, you first need the sum of all orders per day, and only then the average across those daily sums. That is conceptually different from a simple average across all order lines, because days with many small orders would otherwise weigh disproportionately.

Many developers intuitively try to implement nested aggregation directly with AVG(SUM(amount)) in a single SELECT statement, only to hit an error, because SQL does not allow directly nesting two aggregate functions without an intermediate step. The correct solution goes through an intermediate aggregation, usually via a subquery or a common table expression, that first computes the first aggregation level before the outer query applies the second aggregate function on top of it.

This article explains why direct nesting is technically forbidden, shows subqueries and CTEs as clean solutions for nested aggregation, distinguishes the concept from window functions, and covers common pitfalls such as empty intermediate aggregates and rounding errors in multi level calculations.

2. Why SQL does not allow direct nesting of aggregate functions

The reason nested aggregation does not work directly via AVG(SUM(column)) at the same query level lies in the execution order of SQL queries. Aggregate functions operate on a set of rows within a group, and the result of an aggregate function is exactly one value per group. A second aggregate function applied directly on top would only have a single value available, over which there would be nothing left to aggregate, which is why most database systems reject this construction with an error.

The actual goal of nested aggregation, however, is to aggregate again across multiple group values, not across a single already reduced value. To evaluate AVG(SUM(amount)) meaningfully, the database first needs to compute SUM(amount) per day (or per another intermediate group) and provide these intermediate values as their own rows, before AVG can be applied to this set of intermediate values. These two separate steps must be explicitly formulated in SQL as two query levels.

-- This fails in virtually every SQL database: direct nesting is not allowed
SELECT AVG(SUM(total_amount)) AS avg_daily_revenue
FROM orders
GROUP BY DATE(order_date);
-- ERROR: aggregate function calls cannot be nested

3. The solution: a subquery as an intermediate aggregation

The classic solution for nested aggregation uses a subquery in the FROM clause that first computes the first aggregation level and provides it as an independent intermediate table. The outer query then applies the second aggregate function to the result of this subquery, without any direct nesting being needed. This technique is sometimes called a derived table, because the inner query produces a derived, temporary table that only exists for the duration of the outer query.

The decisive advantage of this approach for nested aggregation is that each aggregation level has its own, independent GROUP BY clause. The inner query groups by day and computes the daily total, the outer query no longer has its own GROUP BY clause because it aggregates over the entire result set of the subquery. This pattern can be repeated as often as needed, as long as each new level wraps another subquery around the previous one.

-- Nested aggregation via a subquery: average of daily sums
SELECT AVG(daily_total) AS avg_daily_revenue
FROM (
    SELECT DATE(order_date) AS order_day, SUM(total_amount) AS daily_total
    FROM orders
    GROUP BY DATE(order_date)
) AS daily_sums;

-- Result: a single row with the average revenue per day

4. CTEs for readable, multi level aggregation

A common table expression, or CTE, is often the more readable alternative to a subquery in the FROM clause for nested aggregation, especially once multiple aggregation levels or extra calculation steps are involved. The WITH clause names the first aggregation level, clearly separating it from the second level for the outer query, which makes the code considerably easier to understand than deeply nested subqueries in the FROM clause.

The functional difference between a CTE and a subquery is small on most modern database systems, because the query optimizer usually compiles both forms into a similar execution plan. The advantage of the CTE lies almost entirely in readability and maintainability, which can make the decisive difference between a traceable query and a hard to debug one when nested aggregation involves multiple intermediate steps.

-- Nested aggregation via a CTE: median-like spread of daily order counts
WITH daily_stats AS (
    SELECT
        DATE(order_date) AS order_day,
        COUNT(*)          AS order_count,
        SUM(total_amount) AS daily_total
    FROM orders
    GROUP BY DATE(order_date)
)
SELECT
    AVG(order_count)  AS avg_orders_per_day,
    MAX(order_count)  AS busiest_day_orders,
    AVG(daily_total)  AS avg_daily_revenue,
    STDDEV(daily_total) AS revenue_volatility
FROM daily_stats;

5. Nested aggregation vs. window functions

An important distinction for nested aggregation concerns its boundary with window functions. A window function like SUM(amount) OVER (PARTITION BY customer) adds an extra aggregated value to every single row, without reducing the number of rows. Nested aggregation, by contrast, reduces the row count at every step: the first level reduces individual orders to one row per day, the second level reduces those daily rows to a single overall row.

Anyone who wants to compute an average of daily sums while still seeing every individual order line in the result typically combines both techniques: a window function for the daily total per row, followed by a further aggregation that collapses these repeating daily values for the final average. Nested aggregation with plain subqueries or CTEs, on the other hand, fits exactly the case where only the final, condensed result matters and individual rows are no longer needed.

6. More than two levels: three tier aggregation

The principle of nested aggregation extends easily to three or more levels, by wrapping each additional level as another CTE or subquery around the previous one. A real world example: first revenue per order, then the sum per day, and finally the average of those daily sums per calendar week. Each of these three levels has its own, clearly delimited grouping level, which keeps the query traceable despite multiple aggregation steps.

As the number of levels increases, though, so does the likelihood of logical errors, for instance when an intermediate level accidentally groups by the wrong column or a filter condition is placed at the wrong level. For multi level nested aggregation, it is therefore advisable to test each CTE individually with a separate SELECT before building the next level on top, rather than writing the entire multi level query in one go and only checking it at the end.

-- Three-level nested aggregation: order -> day -> week average
WITH daily_totals AS (
    SELECT DATE(order_date) AS order_day, SUM(total_amount) AS daily_total
    FROM orders
    GROUP BY DATE(order_date)
),
weekly_totals AS (
    SELECT DATE_TRUNC('week', order_day) AS order_week, SUM(daily_total) AS weekly_total
    FROM daily_totals
    GROUP BY DATE_TRUNC('week', order_day)
)
SELECT AVG(weekly_total) AS avg_weekly_revenue
FROM weekly_totals;

7. Common use cases in reports and dashboards

Nested aggregation shows up in practice wherever a metric needs to be condensed across a natural time axis or another grouping level. Typical examples are average monthly revenue from weekly sums, average number of active users per day from hourly counts, or average basket size per customer, computed from the sum of items per order, averaged across all of a customer's orders.

Another common use case is computing variability with nested aggregation: once an intermediate level provides daily revenue, the outer query can compute not just the average, but also the minimum, maximum, and standard deviation of those daily sums. This lets a single, well structured query show both the typical value and the spread of a business process over time, without needing separate queries for each metric.

8. Pitfalls: empty intermediate aggregates and rounding errors

A common pitfall with nested aggregation concerns days with no orders at all. If the inner query only returns days with at least one order, because it groups directly on the orders table, revenue-free days are entirely missing from the intermediate aggregation instead of appearing as a row with the value 0. The outer average then gets systematically inflated, because it effectively only averages across active days, not across the entire period under consideration.

The solution is to build the intermediate aggregation not directly on the orders table, but on a generated date series with a LEFT JOIN to the orders table, so that days without any orders also appear in the intermediate level with revenue of 0. A second, smaller pitfall concerns rounding errors in multi level aggregation: if the first level is already rounded before the second level aggregates over it, rounding errors accumulate across levels. Rounding should therefore generally happen only at the very last, outermost level, not at every intermediate aggregation.

-- Correct nested aggregation: generate a date series so empty days count as 0
WITH date_range AS (
    SELECT generate_series(
        DATE '2026-01-01', DATE '2026-01-31', INTERVAL '1 day'
    )::date AS order_day
),
daily_totals AS (
    SELECT
        d.order_day,
        COALESCE(SUM(o.total_amount), 0) AS daily_total
    FROM date_range d
    LEFT JOIN orders o ON DATE(o.order_date) = d.order_day
    GROUP BY d.order_day
)
SELECT AVG(daily_total) AS avg_daily_revenue_including_empty_days
FROM daily_totals;

9. Approaches to nested aggregation compared

The following overview compares the common techniques for computing aggregates over already aggregated values, and clarifies when each approach makes sense for nested aggregation.

In most cases a CTE is the clearest choice for nested aggregation, because it explicitly names every aggregation level and stays traceable even with three or more levels. Subqueries in the FROM clause are equivalent with just two levels, but quickly lose clarity once additional levels or filter conditions are added.

Recognizing nested aggregation as its own, nameable pattern, rather than treating it as a special case, leads to reporting queries that correctly distinguish from the start between individual rows, intermediate aggregates, and final metrics.

10. Summary

Nested aggregation computes a metric from already aggregated intermediate values, such as the average of daily sums. Because SQL forbids directly nesting aggregate functions like AVG(SUM(column)) at the same query level, the correct path goes through a subquery or a CTE that first provides the first aggregation level, before the outer query applies the second aggregate function on top.

CTEs are usually the clearer choice for multi level nested aggregation, because every level is explicitly named. Missing intermediate values, such as days without orders, must be caught with a generated date series and a LEFT JOIN, otherwise the outer average gets systematically skewed. Rounding belongs exclusively at the last level, not at every intermediate aggregation.

11. FAQ: Nested Aggregation