Why Averaging Your Averages Gives the Wrong Number in SQL

Five department averages, one wrong company average

You compute each department's average salary, then average those five numbers to get a company-wide figure. It's off by nearly $2,000 from the real answer, no error, no warning, no obviously wrong-looking row.

That returns 85738.33. Now average the salary column directly, with no detour through per-department averages first:

That returns 87650. Same table, same 20 employees, same underlying salary numbers, and the two numbers disagree by $1,911.67. The first query isn't buggy SQL, it computes exactly what it says: the average of five averages. The problem is that "the average of the averages" and "the average of everyone" are different questions, and they only ever produce the same number by coincidence.

Why: an average is a number that forgot its weight

An average of [a, b, c] collapses however many rows went into it down to one number. Once you have that number, there's no way to tell, just by looking at it, whether it came from 3 rows or 3,000. Averaging a list of already-averaged numbers treats a department of 3 people and a department of 20 as equally important, because by the time you're averaging averages, "how many people" has already been thrown away.

Department 1 has 5 employees averaging $110,400, the highest average and the biggest headcount in the company. Department 4 has only 3 employees averaging $72,166.67. In the true company-wide average, department 1's higher salaries pull harder on the result because more people earn them. In the naive average of the five dept_avg numbers, department 1's $110,400 and department 4's $72,166.67 count for exactly the same one-fifth each, even though department 1 represents five people's paychecks and department 4 represents three. That's why the naive number (85738.33) undershoots the real one (87650): it under-weights the department that actually has the most highly-paid people in it.

The fix when you have the raw rows: just average the column

If the raw rows are sitting right there, as they are in employees, the fix is to not pre-aggregate at all. AVG(salary) over the whole table, shown above, is already correct. The mistake only happens when someone groups first out of habit, or because they're building a report one department at a time and then need a company-wide total afterward.

The fix when you only have the group averages: weight by count

Sometimes you don't have the raw rows anymore, only a summary someone already handed you: one row per department, an average, and a headcount. That's the shape most dashboards and CSV exports actually give you. You can still get the correct overall average from that summary, but AVG() of the average column is the wrong tool. What you need is a weighted average: multiply each group's average back out by its row count, add those up, and divide by the total row count.

That returns 87650.00, matching the true average exactly, computed entirely from the five summary rows with no access to the original 20. dept_avg * headcount reconstructs each department's total payroll, SUM(...) adds the five totals back into the company-wide total, and dividing by SUM(headcount) divides by the real number of people instead of the number of departments. This is the pattern to reach for any time someone hands you pre-aggregated averages and asks for an overall figure: you can't average the averages, but you can always weight them back up if the row count that produced each one is still attached.

The edge case that makes this worse: NULLs shrink the weight silently

AVG() already excludes NULLs from both the sum and the count it divides by, which is correct on its own. But it means the "headcount" you'd naturally reach for, COUNT(*), can overstate the actual weight a group's average deserves if that column has gaps. Both departments below have 5 rows, but department 2 only has 1 non-NULL salary; the other 4 employees have a NULL in that column, so AVG(salary) for department 2 is $90,000 built from a single value, not five:

Weighting by row_count (5 and 5, treating both departments as equally well-represented) gives 95000.00. Weighting by nonnull_salaries (5 and 1, the number of real values each average was actually built from) gives 98333.33. They disagree because department 2's single real salary got stretched to count as five when weighted by row count, pulling the result toward $90,000 far more than one real salary should. The fix is to weight by the same denominator AVG actually used internally: COUNT(salary), the non-NULL count, not COUNT(*), the row count. If a group's average was built from fewer real values than it has rows, its weight in a reconstructed overall average has to reflect that, or you're back to the exact mismatch this whole post is about, just hidden one level deeper.

Where to go next

The AVG reference and ROUND reference cover the syntax of each function on its own. For the closely related trap where COUNT(*) is used as a denominator instead of COUNT(column), see why COUNT(*) and COUNT(column) give different answers, it's the same "the denominator has to match what actually went into the sum" rule, applied one level before you ever get to averaging averages. The GROUP BY with HAVING example and the practice problems are good places to build reports that aggregate in stages without this bug creeping in.