Aggregating DISTINCT Values
Use SUM(DISTINCT ...) and AVG(DISTINCT ...) to avoid double-counting repeated values.
Overview
Aggregates accept DISTINCT to collapse duplicates before reducing. This is
less common than COUNT(DISTINCT …) but useful when repeated values would skew
a sum or average — for example, summing only the distinct prices that appear in
orders rather than weighting by quantity.
The Query
Store (Customers/Orders/Products)
Loading database engine...
Step-by-Step Breakdown
COUNT(DISTINCT product_id)— how many unique products were ordered.SUM(DISTINCT p.price)— adds each distinct price once, ignoring how many times that price was ordered.AVG(DISTINCT p.price)— averages distinct prices, not order lines.
DISTINCT changes the math
SUM(DISTINCT price) is not the same as total revenue — it deliberately
ignores repetition. Only use it when that's the intent.
Variations
Count distinct customer cities:
Store (Customers/Orders/Products)
Loading database engine...
Common Mistakes
- Accidental DISTINCT.
SUM(DISTINCT amount)can silently under-report if you meant to sum every row. - DISTINCT on multi-column sums.
SUM(DISTINCT a + b)distincts the combined expression, not each column. - Performance.
DISTINCTforces a de-duplication pass; avoid it when a plain aggregate is correct.