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
Loading playground environment...
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:
Loading playground environment...
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.
Cite this resource
SQLSimplified. "Aggregating DISTINCT Values Example". Available at: https://sqlsimplified.online/examples/aggregate-distinct