Course contentsShow
← Data Science
Lesson 887 of 2,14523. SQL Aggregation and GroupingFree lesson

Aggregates with DISTINCT

Using DISTINCT within aggregate functions like COUNT(DISTINCT) and SUM(DISTINCT) to handle unique values.

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(*) vs COUNT(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.