Aggregation & GROUP BY
Concept  ·  SQL & Querying

An average of group averages weights every group equally regardless of size — it is not the overall average, and nothing errors.

Each group's average has already thrown away its denominator, so averaging the averages treats a 3-measurement stop and a 1-measurement stop as equal voters. The two numbers answer different questions — 'what's the typical stop like?' (equal-weighted) vs 'what's the typical wait like?' (size-weighted) — and the sin is mislabeling the first as the second.

See it break
waits
stopmins
a10
a10
a10
b40
The trap
SELECT
  ROUND(AVG(stop_avg), 2) AS wait
FROM
  (
    SELECT
      stop,
      AVG(mins) AS stop_avg
    FROM
      waits
    GROUP BY
      stop
  );
The fix
SELECT
  ROUND(AVG(mins), 2) AS wait
FROM
  waits;
The trap
25
The fix
17.5

The average wait read 25.0, not 17.5 — averaging each stop's average weighted a 3-visit stop and a 1-visit stop equally, so the busy stop's real waits were undercounted.

The rule

Aggregate raw rows for overall figures; weighted reconstruction (SUM(sums)/SUM(counts)) from rollups; label equal-weighted figures as per-group.

Shows up elsewhere
COUNT(*) vs COUNT(col) vs COUNT(DISTINCT col)SUM over an empty / all-NULL group
Try one →Prove it in practice →