← All cheatsheets
Data Analyst · #095 · November 8, 2026 · 1 min read

Average of averages: why your dashboard says $80 when the real number is $41

Why AVG() on a column that is already an average gives the wrong number, the sum and count fix in SQL, the rescue when only the averages were kept, and where the same trap hides: one page.

Get the free PDF

One page, print-ready, free to share. No signup needed.

Download the PDF

Your dashboard says the average order is $80. Finance says $41. Both used the same table. This page covers why averaging averages gives the wrong number, the fix in SQL, the rescue when only the averages were kept, and where the same trap hides. The print-ready A4 PDF is at the bottom.

The bug

  • Store A: 1,000 orders, average $40, so $40,000 of revenue.
  • Store B: 10 orders, average $120, so $1,200 of revenue.
  • (40 + 120) / 2 = 80: the dashboard gives store B's 10 orders the same weight as store A's 1,000.

The fix

Keep the sum and the count. Divide at the very end.

-- wrong: 80.00
SELECT AVG(avg_order) FROM store_daily;

-- right: 40.79
SELECT SUM(revenue) / SUM(orders)
FROM store_daily;

$41,200 divided by 1,010 orders is $40.79. That is the average order.

If the summary table only kept the average, add the weight back: SUM(avg_order * orders) / SUM(orders) gives the same 40.79. If the count is gone too, go back to the order rows.

Where the same trap hides

  • Conversion rates: averaging seven daily rates into a weekly rate. A Monday with 200 visitors and a Saturday with 9,000 count the same.
  • Basket per region, rating per category, time on site per country: any ratio that is grouped twice.
  • Percentages in a pivot table: the grand total of an "average of" column is the average of the rows, not the ratio of the totals.

The rule fits on one line: never AVG an average. Keep the numerator and the denominator, divide last.

Frequently asked questions

Why is the average of averages wrong?
Each average hides how many rows it was computed on. Averaging them gives every group the same weight, so a store with 10 orders counts as much as a store with 1,000. Store A at $40 on 1,000 orders and store B at $120 on 10 orders average to $80, while the real average order is $41,200 divided by 1,010 orders, $40.79.
How do I compute the right average in SQL?
Divide the total by the count at the very end: SUM(revenue) / SUM(orders). Never AVG() a column that is already an average or a rate.
What if my summary table only kept the average?
Put the weight back: SUM(avg_order * orders) / SUM(orders). If the count was not kept either, the right number cannot be rebuilt from that table; go back to the rows.
When is the average of averages acceptable?
Only when every group has the same number of rows, or when the question really is about the typical group rather than the typical order (for example, how the average store performs). Say which one you are reporting.

Get the free PDF

One page, print-ready, free to share. No signup needed.

Download the PDF

More cheatsheets