Aggregating Distinct Values in Groups

In this lesson, you will learn how to count or process only the unique items within specific categories, rather than every duplicate entry in your dataset.

The Challenge of Duplication

In many analytical scenarios, you need to measure the variety of data within a category. For example, imagine a retail database containing thousands of sales records. If you simply count the number of rows for each product category, you get the total sales volume. But what if you want to know how many unique customers bought products in each category? Using a standard COUNT() would overcount any customer who made multiple purchases.

Using DISTINCT with Aggregates

To solve this, SQL allows you to place the DISTINCT keyword directly inside an aggregate function. This tells the database engine to collapse duplicate values into a single instance before performing the calculation.

Consider a table of orders with columns category and customer_id. If we want to see how many unique customers shopped in each category, we use this query:

SELECT 
    category, 
    COUNT(DISTINCT customer_id) AS unique_shoppers
FROM orders
GROUP BY category;

How it Works

When the database encounters COUNT(DISTINCT customer_id), it follows these internal steps:

1. Grouping: It partitions the data into the buckets defined by your GROUP BY clause.
2. Filtering: Within each bucket, it ignores any customer_id that has already been counted, keeping only the first unique instance it encounters.
3. Calculating: It performs the count on the remaining set of unique IDs.

This technique is not limited to COUNT(). You can also use SUM(DISTINCT column_name) if you need to total up unique values, though this is less common. The key is recognizing that DISTINCT changes the scope of the aggregation from "every row" to "every unique identifier." By combining this with GROUP BY, you gain the ability to generate reports that describe the breadth of your data rather than just the volume.

Key Takeaway: By placing DISTINCT inside your aggregate function, you filter out redundant data points within your groups, ensuring your metrics reflect unique occurrences instead of raw row counts.