Unit 04.03: The average-of-averages error

Unit ID: SQL-M04-U04 - Estimated active time: 13-16 minutes Objective: recognise when averaging grouped averages produces a wrong overall figure, and compute it correctly.

Two numbers that should match, and do not

The correct average order value across all orders:

SELECT ROUND(AVG(order_total), 2) FROM orders WHERE status = 'completed';
-- 2718.53

Now the same idea computed by averaging the per-status averages:

SELECT ROUND(AVG(a), 2)
FROM (
  SELECT status, AVG(order_total) AS a
  FROM orders
  GROUP BY status
) t;
-- 2007.51

₹711 apart. Neither query errored. Both look like "the average order value".

Why it happens

The per-status averages are:

SELECT status, COUNT(*) AS orders, ROUND(AVG(order_total), 2) AS avg_total
FROM orders
GROUP BY status;
-- completed | 988 | 2718.53
-- pending   |  12 | 1296.50

Averaging 2,718.53 and 1,296.50 treats a group of 988 and a group of 12 as equally important. The 12 pending orders get 50% of the weight. That is the error: an unweighted mean of means ignores group size.

Doing it correctly

If you genuinely need to combine group-level results, weight by the group size - or better, aggregate from the original rows:

-- Correct: aggregate the underlying rows
SELECT ROUND(AVG(order_total), 2) FROM orders;
-- 2701.46   (all 1,000 orders, both statuses)

-- Equivalent, weighted from the groups
SELECT ROUND(SUM(total) / SUM(n), 2)
FROM (
  SELECT status, COUNT(*) AS n, SUM(order_total) AS total
  FROM orders
  GROUP BY status
) t;
-- 2701.46

Carrying SUM and COUNT rather than AVG is what makes the second version recombinable. An average cannot be re-averaged; a sum and a count can always be re-divided.

Non-example

Averaging averages is fine when the groups are genuinely the same size, or when you deliberately want each group to count equally - for example "the average of each region's satisfaction score, treating regions as equal units". State that intent, because a reader will assume the weighted meaning.

Practice

You are given per-country average order values and asked for "the overall average". What do you request from whoever produced the table, and why?

Takeaway

Never average an average. Carry SUM and COUNT through your intermediate results so the final division can be done correctly.

---

Mark complete