skip to content

In a ROLLUP report, why doesn't the grand-total AVG equal the average of the subtotal AVGs?

level: seniorimportance: nice to knowfreq 30%

answer

  1. Levels are independent, not stacked
  2. Group sizes are not equal
  3. Weighted versus unweighted mean
  4. Some measures re-add, some do not
  5. SUM survives, AVG and DISTINCT counts do not

basics

~20 s

Each grouping set is computed independently from the base rows, so the grand-total AVG is the sum of all values over the count of all rows. Averaging the subtotal averages instead weights every group equally.

solid answer

~50 s

Super-aggregate rows are not derived from the level beneath them: every grouping set is evaluated over the same underlying rows that survived `WHERE`. So the grand-total `AVG(amount)` is `SUM(all amounts) / COUNT(all rows)`, whereas averaging the per-region averages gives each region equal weight regardless of how many rows it contains. The two agree only when all groups are the same size. This is the general distinction between **additive** measures — `SUM` and `COUNT(*)`, which really can be re-added across disjoint groups — and non-additive ones such as `AVG`, `COUNT(DISTINCT …)`, medians and ratios, which cannot. The practical rule: never let a client or a spreadsheet re-aggregate the subtotal rows of a grouping-set result. Ask the engine for the level you want, and express ratios as `SUM(numerator) / SUM(denominator)` so each level computes its own correct value.

code

sql · 8 lines
sql
-- sales holds ('EU',1), ('EU',1), ('EU',10), ('US',8)
SELECT region, AVG(amount) AS avg_amount, SUM(amount) AS total, COUNT(*) AS n
FROM sales
GROUP BY ROLLUP(region);

-- EU   |  4 | 12 | 3
-- US   |  8 |  8 | 1
-- NULL |  5 | 20 | 4    <- AVG is 20/4 = 5, not (4+8)/2 = 6

go deeper

for a junior

Remember that each level of a ROLLUP is calculated from the original rows, so the grand-total average is the average of all values, not the average of the averages shown above it.

for a middle

Explain the weighted-versus-unweighted arithmetic with a small example, and classify which aggregates are additive over disjoint groups and which are not.

for a senior

Diagnose the real failures: dashboards summing every level together, client-side re-aggregation of averages, ratios written as averages of ratios. Show the quotient-of-sums formulation and level filtering with GROUPING().

for a principal

Own the summary-table contract: store additive components so any coarser level is derivable, define which level each consumer reads, and make the query the single authority on multi-level numbers rather than the reporting tool.

## Every level reads the base rows A grouping-set query does not build a pyramid. `GROUP BY ROLLUP(region)` defines two grouping sets, `(region)` and `()`, and both are evaluated over the same set of rows that passed `WHERE`. The grand-total row is not assembled from the subtotal rows; it is an independent aggregate over everything. For `SUM` and `COUNT(*)` the distinction is invisible, because those functions are **additive** over disjoint groups: the sum of the parts is the sum of the whole. For `AVG` it is not. ## The arithmetic Take `sales(region, amount)` holding `('EU',1)`, `('EU',1)`, `('EU',10)`, `('US',8)`: ```sql SELECT region, AVG(amount) AS avg_amount, COUNT(*) AS n FROM sales GROUP BY ROLLUP(region); -- EU | 4 | 3 -- US | 8 | 1 -- NULL | 5 | 4 <- grand total: 20/4, not (4+8)/2 ``` The grand total is 5. The average of the two subtotal averages is 6. Neither is wrong — they answer different questions. `5` is the average sale; `6` is the average of the regional averages, an unweighted mean that treats a one-row region as equal in weight to a three-row one. If a report needs the second number it must ask for it explicitly, typically by aggregating twice with a derived table. ## Additive, semi-additive, non-additive It is worth carrying the vocabulary: - **Additive**: `SUM`, `COUNT(*)`. Correctly re-derivable by adding the level below across disjoint groups. - **Non-additive**: `AVG`, `MIN`/`MAX` of a ratio, medians and percentiles, `COUNT(DISTINCT …)`. Distinct counts are the sharpest example: a customer who bought in both EU and US counts once in each region's distinct count and once — not twice — in the grand total, so the subtotals sum to more than the total. The ROLLUP result itself is correct at every level; only re-adding it is wrong. - Ratios and rates are non-additive too, and they are the ones that silently produce plausible wrong numbers. ## Ratios: compute them per level The safe formulation for a rate is a quotient of two additive measures, so each grouping set derives its own value from its own rows: ```sql SELECT region, SUM(CASE WHEN status = 'FAILED' THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS failure_rate FROM orders GROUP BY ROLLUP(region); ``` The grand-total row here is the true overall failure rate. Averaging the per-region rates instead would give small regions disproportionate influence — the classic weighted-versus-unweighted mistake behind many disputed dashboards. ## Where this bites in practice Three recurring failures: 1. A BI tool or spreadsheet is pointed at the full grouping-set result and told to sum a column. It adds detail rows, subtotal rows and the grand total together, inflating everything several-fold. The fix is to filter to one level — `HAVING GROUPING(region) = 0` — or to have the tool consume only the level it needs. 2. Someone "optimises" a report by fetching subtotals and computing the total client-side. `SUM` survives; `AVG` and distinct counts quietly do not. 3. A cached summary table stores per-day averages, and a monthly figure is produced by averaging thirty daily averages. Storing `SUM` and `COUNT` per day instead makes every higher level exactly derivable — the standard way to make a summary table safely re-aggregatable. ## Interview framing The question is really about understanding what a super-aggregate row *is*: not a summary of the rows above it in the printout, but an independent aggregate at a coarser grouping. Say that first, then give the weighted-versus-unweighted arithmetic, then generalise to which measures are additive. A strong answer also mentions that the same reasoning governs whether a materialised summary can serve a coarser query at all — store additive components, and every level follows; store an average, and you have thrown away the weights.

  • Which aggregates in a ROLLUP result can safely be re-added by a client, and which cannot?
    SUM and COUNT(*) are additive over disjoint groups and can be re-added. AVG, COUNT(DISTINCT …), medians, percentiles and any ratio cannot: distinct counts overlap across groups, and averages need their weights. If a consumer must derive coarser levels itself, give it SUM and COUNT columns and let it divide.
  • How would you actually produce an unweighted average of the per-region averages?
    Aggregate twice: compute the per-region averages in a derived table or CTE, then average that result. `SELECT AVG(region_avg) FROM (SELECT region, AVG(amount) AS region_avg FROM sales GROUP BY region) t`. That is a different measure from the overall average, so label it clearly in the report — the two numbers disagreeing is otherwise read as a bug.
  • A dashboard that sums a column over a ROLLUP result shows numbers several times too large. What happened?
    The tool is summing detail rows, subtotal rows and the grand total together, so every value is counted once per level it appears in. The result set is correct; the consumer is. Filter to a single level with `HAVING GROUPING(...) = 0`, or return a level indicator and have the tool select one level before aggregating.

saying these in an interview costs you the question

  • Says the grand total is computed from the subtotal rows
  • Treats AVG as additive across groups
  • Assumes subtotal distinct counts add up to the overall distinct count
  • Averages per-day rates to get a monthly rate
  • Feeds a whole grouping-set result into a client-side SUM

context

resolving links…