ROUND(AVG(x)) vs AVG(ROUND(x)) in SQL

Same data, two ways to round, two different numbers

You need a monthly figure from an annual salary, so you divide by 3 for a quarterly stipend, round it, then average across the team. Someone else on your team writes what looks like the same report, but rounds only the final average instead of each row first. Neither query has a typo. They still don't agree.

The first returns 29216.67. The second returns 29216.75. Both are "correct" for what they actually compute, they just compute different things: ROUND(AVG(x)) rounds once, at the very end, after every exact value has already been summed and divided. AVG(ROUND(x)) rounds every row first, permanently baking each row's individual rounding error into the number that gets averaged. Rounding and averaging don't commute, and the two answers only ever match by coincidence.

Why: every row you round nudges the total, and the nudges don't cancel

Rounding a value to the nearest whole number moves it up or down by whatever its fractional part was. 29000.00 doesn't move at all. 24666.67 moves up by 0.33. 25333.33 moves down by 0.33. If a batch of values has exactly as many round-ups as round-downs of exactly the same size, those nudges cancel and AVG(ROUND(x)) matches ROUND(AVG(x)) by luck. Real data almost never balances that neatly. Across the 20 salaries above, dividing by 3 produces fractional parts that lean more often toward .67 than .33, so more rows get nudged up than down, and the rounded-first average drifts upward by 8 cents. Rounding the exact average instead only ever applies one nudge, to one number, at the end, so nothing has a chance to accumulate.

The worst case: when every value is a tie

The 8-cent gap above is mild because most fractional parts aren't close to a rounding boundary. The gap gets as large as possible when every value sits exactly on .5, the one point where standard rounding always breaks the same direction. A product with half-star ratings shows this cleanly:

The four exact ratings average to a clean 4.00. But ROUND(stars, 0) rounds every .5 up (that's "round half away from zero," the default in most SQL engines, not banker's rounding), so 4.5 becomes 5 and 3.5 becomes 4 for every single row, no row ever rounds down. The rounded-first average comes out to 4.50, half a star higher than the real number, purely because half-star values are the one case where rounding is never neutral. This is exactly what happens if a review system stores whole-star badges computed row by row and someone later averages the badges instead of the original half-star ratings.

It doesn't go away with GROUP BY

Grouping doesn't fix the mismatch, it just runs the same two calculations once per group instead of once overall:

Every department's two columns disagree by a few cents, in either direction, depending on how many of that department's rows happened to round up versus down. None of the disagreements are large on their own. The problem is that they're silent: nothing here throws an error or looks obviously wrong, so a report built the "round first" way sits next to the correct one for months before anyone notices the totals don't reconcile. This is a different bug from averaging an already-grouped average, covered in why averaging your averages gives the wrong number, but the two stack: pre-aggregating and pre-rounding are both ways of throwing away information before the final calculation sees it.

The division that makes this example possible

salary / 3 above returns a decimal, not a truncated integer, because DuckDB, which runs the playgrounds on this page, changed / to always perform true division, even when both sides are integers, the same rule Python 3 uses. That's a deliberate departure from older SQL behavior.

Where to go next

The ROUND reference and AVG reference cover each function's syntax on its own, including how AVG already excludes NULLs from the count it divides by. The GROUP BY average example is a shorter, syntax-focused version of the grouped case above. If you're building a report that combines both aggregation and rounding, the practice problems have several exercises worth running through the two versions of this query yourself, rounding first and rounding last, until picking the right one stops being a guess.