Averages: the four ways AVG misleads

A mean is a sum divided by a count, and each half can go wrong

avg(amount) looks like the safest function in SQL. It is the one most often wrong in a way nobody notices, because an average is always a plausible-looking number. There are four distinct mechanisms, and each has a different fix.

One: integer division

Type SELECT 7 / 2 into Postgres and you get 3. SQLite and SQL Server also say 3. MySQL says 3.5000 and DuckDB says 3.5. The expression is identical; the engines disagree about whether dividing two integers should produce an integer.

This matters the moment you compute an average by hand:

SELECT sum(qty) / count(*) AS avg_qty FROM order_items;   -- 2, in Postgres
SELECT avg(qty)            AS avg_qty FROM order_items;   -- 2.4375

sum(qty) is an integer, count(*) is an integer, and the division truncates before you ever see it. avg() is safe because it promotes to numeric internally. If you must divide, cast one side first: sum(qty)::numeric / count(*). The bug hides best in percentages, where paid / total * 100 produces 0 for every row under 100%, because paid / total is 0 before the multiplication happens.

Two: the average of averages

A regional manager asks for the average order value across two branches.

• Branch A: 100 orders, average ₹500. Total ₹50,000.
• Branch B: 10 orders, average ₹5,000. Total ₹50,000.

Average the two branch averages and you get ₹2,750. Compute the true average and you get ₹100,000 over 110 orders, which is ₹909. The first number is not an approximation of the second; it is an answer to a different question (“what is the average branch’s average?”), and it is nearly three times larger.

This happens whenever a summary table is summarised again. A daily_stats table with an avg_order_value column cannot be averaged to get the monthly figure. You need the daily sum and the daily count stored separately, then sum(total) / sum(orders). Sums and counts combine; averages do not. The same rule breaks percentiles, which the next lesson covers, and it is why a well-designed summary table stores components rather than results.

Three: the weight that was not applied

“Average price per unit” is not avg(unit_price). If one line sells 1,000 units at ₹10 and another sells 1 unit at ₹1,000, avg(unit_price) is ₹505, and the real price people paid per unit is ₹11,000 over 1,001 units, which is about ₹10.99.

SELECT sum(qty * unit_price) / sum(qty) AS price_per_unit
FROM order_items;

The pattern is always the same: multiply each value by its weight, sum, and divide by the sum of the weights. Any time the rows in your pile are not equally important (orders of different sizes, days with different traffic, students taking different numbers of exams) an unweighted avg() answers a question about rows, not about the thing you care about.

Four: rounding at the wrong moment

Round at the end, never in the middle. A query that rounds each row to two decimals and then sums 40,000 rows carries up to 40,000 half-cent errors. Postgres, when it rounds numeric, rounds ties away from zero, so round(2.5) is 3; Python’s round(2.5) is 2, because it rounds ties to even. A figure computed in SQL and re-checked in a notebook can legitimately disagree by one unit in the last place, and knowing that saves an afternoon.

And the one everyone knows

The mean sits where nobody is when the distribution has a long tail. The earlier lesson on a right number versus a useful number made that point; the operational rule is that avg() on money, latency, or anything with outliers should be reported next to a median, and the next lesson shows how to get one.

The habit

Before writing avg(x), ask three things. Is x an integer that will be divided by an integer? Is each row equally important, or does it need a weight? And is this average being computed from rows, or from a table that already contains averages? A yes to the first, a no to the second, or a yes to the third means avg() is the wrong tool, and the fix is a sum and a count you control yourself.