SQL: Why Averaging Averages Gives the Wrong Result

The symptom

You calculate an average for each group, then average those averages to get an overall number. The result looks reasonable, but it does not match the real overall average.

Why it happens

An average of averages gives each group the same weight, even when the groups have different row counts. A category with 2 products counts as much as a category with 20 products.

That is only correct when the business question intentionally weights each group equally. Most "overall average" questions should weight each row equally.

The fix

Compute the average once, over the rows themselves, so that every row carries the same weight into the result. Both queries below report an average product price for Garden Shop.

with category_prices as (
  select category_id, avg(price) as avg_price
  from products
  group by category_id
)
select round(avg(avg_price), 2) as average_price
from category_prices

select round(avg(price), 2) as average_price
from products

Garden Shop stocks 24 products across eight categories, and the categories are nothing like the same size: five houseplants averaging $22.80 against three seed packets averaging $4.75. Averaging the eight category averages lets the seed shelf pull exactly as hard as the houseplant shelf, and that is the whole $0.66 gap.

If you are already holding a grouped result and cannot go back to the rows, carry the numerator and denominator forward instead of the average. Summing the totals and the counts separately, then dividing at the end, gets you the row-weighted answer without rescanning the base table.

with category_prices as (
  select category_id,
         sum(price) as total_price,
         count(*) as product_count
  from products
  group by category_id
)
select round(sum(total_price) / sum(product_count), 2) as average_price
from category_prices

Learn more

• Lesson 4.2 · SUM and AVG
• Lesson 10.2 · Percentages and rates
• Reference · Aggregates
• Interview · GROUP BY questions