Aggregates with DISTINCT
What you'll learn: How to combine DISTINCT with aggregate functions to calculate statistics based only on unique values.
The Core Idea
Sometimes you don't want to count or sum all values—just the unique ones. By placing DISTINCT inside an aggregate function, you tell SQL to eliminate duplicates before performing the calculation.
Think of it like counting unique visitors to a store rather than total visits. If the same person walks in three times, they're still just one unique visitor.
How It Works
You can combine DISTINCT with most aggregate functions you've learned:
COUNT(DISTINCT column)
Counts only unique values, ignoring duplicates:
SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM orders;
This tells you how many different customers placed orders, not how many orders total.
SUM(DISTINCT column)
Adds up each unique value only once:
SELECT SUM(DISTINCT product_price) AS sum_of_unique_prices
FROM sales;
If a product appears multiple times, its price is added only once.
Other Aggregates
You can use DISTINCT with AVG, MIN, and MAX too, though it's less common since duplicates don't affect MIN/MAX results, and AVG(DISTINCT) has specific use cases.
When to Use It
- Counting unique entities: Unique customers, products, or categories
- Avoiding double-counting: When your data has intentional duplicates but you need unique-value statistics
- Data quality checks: Comparing
COUNT(*)vsCOUNT(DISTINCT column)reveals how many duplicates exist
Key Takeaway: Adding DISTINCT inside aggregate functions like COUNT(DISTINCT column) or SUM(DISTINCT column) ensures you're calculating based on unique values only, preventing duplicates from skewing your results.