{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"averages-that-lie","title":"Averages: the four ways AVG misleads","url":"https://addaly.com/learn/data-and-sql/averages-that-lie","position":25,"of":66,"minutes":9,"answer":"An average is a sum divided by a count, and both halves can be silently wrong: integers truncate, unequal rows need weights.","keyIdea":"An average is a sum divided by a count, and both halves can be silently wrong: integers truncate, unequal rows need weights, and averages of averages answer a different question than the average of the rows."},"body":{"format":"markdown","text":"## A mean is a sum divided by a count, and each half can go wrong\n\n`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.\n\n## One: integer division\n\nType `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.\n\nThis matters the moment you compute an average by hand:\n\n```sql\nSELECT sum(qty) / count(*) AS avg_qty FROM order_items;   -- 2, in Postgres\nSELECT avg(qty)            AS avg_qty FROM order_items;   -- 2.4375\n```\n\n`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.\n\n## Two: the average of averages\n\nA regional manager asks for the average order value across two branches.\n\n- Branch A: 100 orders, average ₹500. Total ₹50,000.\n- Branch B: 10 orders, average ₹5,000. Total ₹50,000.\n\nAverage 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.\n\nThis 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.\n\n## Three: the weight that was not applied\n\n\"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.\n\n```sql\nSELECT sum(qty * unit_price) / sum(qty) AS price_per_unit\nFROM order_items;\n```\n\nThe 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.\n\n## Four: rounding at the wrong moment\n\nRound 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.\n\n## And the one everyone knows\n\nThe 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.\n\n> Figure: Two branches, and the average of their averages. Bars (rupees per order): Branch A, 100 orders 500; Branch B, 10 orders 5000; Average of the two averages 2750; All 110 orders, summed then divided 909. Branch A took 100 orders and branch B took 10, so the two branch averages are not equally important. Averaging them answers a different question (what is the average branch's average) and gives an answer three times the truth. Sums and counts combine; averages do not.\n\n## The habit\n\nBefore 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."},"check":{"question":"A `daily_stats` table stores, for each day, `orders` and `avg_order_value`. An analyst computes last month's average order value as `avg(avg_order_value)` over the month's 31 rows. What is wrong with that figure?","options":[{"text":"Nothing, provided every day had at least one order, since the average of 31 daily averages is by definition the monthly average and any weighting would only introduce bias.","correct":false,"tempting":"Treats averages as combinable like sums; they only coincide when every day has exactly the same number of orders, which never happens."},{"text":"It weights a Sunday with 40 orders the same as a Monday with 900, so it answers \"what was the average day's average?\" rather than the average across all orders.","correct":true},{"text":"It is truncated by integer division, because `avg()` over an integer column returns an integer.","correct":false,"tempting":"`avg()` promotes to a decimal type in every major engine; integer truncation is a hazard of `sum() / count()`, not of `avg()`."},{"text":"It double-counts, because each day's average already includes the previous day's orders in a running total.","correct":false,"tempting":"Invents a running total that is not in the table; a daily average is computed from that day's rows only."}]},"prev":{"slug":"null","title":"NULL, and why your counts are wrong","url":"https://addaly.com/learn/data-and-sql/null"},"next":{"slug":"percentiles-and-distributions","title":"Percentiles: describing a distribution instead of its mean","url":"https://addaly.com/learn/data-and-sql/percentiles-and-distributions"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Averages: the four ways AVG misleads', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/averages-that-lie (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Averages: the four ways AVG misleads","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":3,"slug":"summaries-and-the-piles-behind-them","title":"Summaries, and the piles behind them"},"answer":null,"keyIdea":"An average is a sum divided by a count, and both halves can be silently wrong: integers truncate, unequal rows need weights, and averages of averages answer a different question than the average of the rows.","hero":{"kind":"figure","figure":{"t":"figure","label":"Figure","figure":{"type":"bars","unit":"rupees per order","title":"Two branches, and the average of their averages","series":[{"label":"Branch A, 100 orders","value":500},{"label":"Branch B, 10 orders","value":5000},{"label":"Average of the two averages","value":2750,"highlight":true},{"label":"All 110 orders, summed then divided","value":909}],"caption":"Branch A took 100 orders and branch B took 10, so the two branch averages are not equally important. Averaging them answers a different question (what is the average branch’s average) and gives an answer three times the truth. Sums and counts combine; averages do not."},"alt":"Figure: Two branches, and the average of their averages. Bars (rupees per order): Branch A, 100 orders 500; Branch B, 10 orders 5000; Average of the two averages 2750; All 110 orders, summed then divided 909. Branch A took 100 orders and branch B took 10, so the two branch averages are not equally important. Averaging them answers a different question (what is the average branch’s average) and gives an answer three times the truth. Sums and counts combine; averages do not."}},"blocks":[{"t":"h2","id":"a-mean-is-a-sum-divided-by-a-count-and-each-half-can-go-wron","runs":[{"text":"A mean is a sum divided by a count, and each half can go wrong"}]},{"t":"p","runs":[{"text":"avg(amount)","code":true},{"text":" 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."}]},{"t":"h2","id":"one-integer-division","runs":[{"text":"One: integer division"}]},{"t":"p","runs":[{"text":"Type "},{"text":"SELECT 7 / 2","code":true},{"text":" into Postgres and you get "},{"text":"3","code":true},{"text":". SQLite and SQL Server also say "},{"text":"3","code":true},{"text":". MySQL says "},{"text":"3.5000","code":true},{"text":" and DuckDB says "},{"text":"3.5","code":true},{"text":". The expression is identical; the engines disagree about whether dividing two integers should produce an integer."}]},{"t":"p","runs":[{"text":"This matters the moment you compute an average by hand:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT sum(qty) / count(*) AS avg_qty FROM order_items;   -- 2, in Postgres\nSELECT avg(qty)            AS avg_qty FROM order_items;   -- 2.4375"},{"t":"p","runs":[{"text":"sum(qty)","code":true},{"text":" is an integer, "},{"text":"count(*)","code":true},{"text":" is an integer, and the division truncates before you ever see it. "},{"text":"avg()","code":true},{"text":" is safe because it promotes to "},{"text":"numeric","code":true},{"text":" internally. If you must divide, cast one side first: "},{"text":"sum(qty)::numeric / count(*)","code":true},{"text":". The bug hides best in percentages, where "},{"text":"paid / total * 100","code":true},{"text":" produces "},{"text":"0","code":true},{"text":" for every row under 100%, because "},{"text":"paid / total","code":true},{"text":" is "},{"text":"0","code":true},{"text":" before the multiplication happens."}]},{"t":"h2","id":"two-the-average-of-averages","runs":[{"text":"Two: the average of averages"}]},{"t":"p","runs":[{"text":"A regional manager asks for the average order value across two branches."}]},{"t":"ul","items":[[{"text":"Branch A: 100 orders, average ₹500. Total ₹50,000."}],[{"text":"Branch B: 10 orders, average ₹5,000. Total ₹50,000."}]]},{"t":"p","runs":[{"text":"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."}]},{"t":"p","runs":[{"text":"This happens whenever a summary table is summarised again. A "},{"text":"daily_stats","code":true},{"text":" table with an "},{"text":"avg_order_value","code":true},{"text":" column cannot be averaged to get the monthly figure. You need the daily "},{"text":"sum","i":true},{"text":" and the daily "},{"text":"count","i":true},{"text":" stored separately, then "},{"text":"sum(total) / sum(orders)","code":true},{"text":". 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."}]},{"t":"h2","id":"three-the-weight-that-was-not-applied","runs":[{"text":"Three: the weight that was not applied"}]},{"t":"p","runs":[{"text":"“Average price per unit” is not "},{"text":"avg(unit_price)","code":true},{"text":". If one line sells 1,000 units at ₹10 and another sells 1 unit at ₹1,000, "},{"text":"avg(unit_price)","code":true},{"text":" is ₹505, and the real price people paid per unit is ₹11,000 over 1,001 units, which is about ₹10.99."}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT sum(qty * unit_price) / sum(qty) AS price_per_unit\nFROM order_items;"},{"t":"p","runs":[{"text":"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 "},{"text":"avg()","code":true},{"text":" answers a question about rows, not about the thing you care about."}]},{"t":"h2","id":"four-rounding-at-the-wrong-moment","runs":[{"text":"Four: rounding at the wrong moment"}]},{"t":"p","runs":[{"text":"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 "},{"text":"numeric","code":true},{"text":", rounds ties away from zero, so "},{"text":"round(2.5)","code":true},{"text":" is "},{"text":"3","code":true},{"text":"; Python’s "},{"text":"round(2.5)","code":true},{"text":" is "},{"text":"2","code":true},{"text":", 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."}]},{"t":"h2","id":"and-the-one-everyone-knows","runs":[{"text":"And the one everyone knows"}]},{"t":"p","runs":[{"text":"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 "},{"text":"avg()","code":true},{"text":" on money, latency, or anything with outliers should be reported next to a median, and the next lesson shows how to get one."}]},{"t":"h2","id":"the-habit","runs":[{"text":"The habit"}]},{"t":"p","runs":[{"text":"Before writing "},{"text":"avg(x)","code":true},{"text":", ask three things. Is "},{"text":"x","code":true},{"text":" 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 "},{"text":"avg()","code":true},{"text":" is the wrong tool, and the fix is a sum and a count you control yourself."}]}],"check":{"question":[{"text":"A "},{"text":"daily_stats","code":true},{"text":" table stores, for each day, "},{"text":"orders","code":true},{"text":" and "},{"text":"avg_order_value","code":true},{"text":". An analyst computes last month’s average order value as "},{"text":"avg(avg_order_value)","code":true},{"text":" over the month’s 31 rows. What is wrong with that figure?"}],"options":[{"text":[{"text":"Nothing, provided every day had at least one order, since the average of 31 daily averages is by definition the monthly average and any weighting would only introduce bias."}],"correct":false,"tempting":[{"text":"Treats averages as combinable like sums; they only coincide when every day has exactly the same number of orders, which never happens."}]},{"text":[{"text":"It weights a Sunday with 40 orders the same as a Monday with 900, so it answers “what was the average day’s average?” rather than the average across all orders."}],"correct":true,"tempting":null},{"text":[{"text":"It is truncated by integer division, because "},{"text":"avg()","code":true},{"text":" over an integer column returns an integer."}],"correct":false,"tempting":[{"text":"avg()","code":true},{"text":" promotes to a decimal type in every major engine; integer truncation is a hazard of "},{"text":"sum() / count()","code":true},{"text":", not of "},{"text":"avg()","code":true},{"text":"."}]},{"text":[{"text":"It double-counts, because each day’s average already includes the previous day’s orders in a running total."}],"correct":false,"tempting":[{"text":"Invents a running total that is not in the table; a daily average is computed from that day’s rows only."}]}],"order":[2,1,3,0],"why":null},"prev":{"slug":"null","title":"NULL, and why your counts are wrong","minutes":8},"next":{"slug":"percentiles-and-distributions","title":"Percentiles: describing a distribution instead of its mean","minutes":9}}}