DISTINCT with Aggregates

Objectives

By the end of this lesson, you should be able to:

• Count only unique values with COUNT(DISTINCT column)
• Explain the difference between COUNT(*), COUNT(column), and COUNT(DISTINCT column)
• Combine COUNT(DISTINCT ...) with GROUP BY

💡 Why this matters: “How many orders came from the West region” and “how many different customers placed orders from the West region” are different questions, one repeat customer with five orders should count as five orders, but one customer.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database, using the same orders table as the previous lesson.

COUNT(DISTINCT column)

SELECT COUNT(DISTINCT department) AS distinct_departments FROM employees;

 distinct_departments
-----------------------
                     3

employees has 8 rows, but only 3 distinct non-NULL department values (Engineering, Marketing, Sales). COUNT(DISTINCT department) counts unique values, not rows, and like plain COUNT(column), it ignores NULL.

Three Different COUNTs

Recall from Module 7: COUNT(*) counts all rows, COUNT(column) counts non-NULL values in that column. COUNT(DISTINCT column) adds a third behavior, counting unique non-NULL values. All three can give different answers on the same table, depending on how many rows there are, how many have a value, and how many of those values repeat.

DISTINCT Inside GROUP BY

SELECT region, COUNT(DISTINCT customer_name) AS unique_customers, COUNT(*) AS total_orders
FROM orders
GROUP BY region
ORDER BY region;

 region | unique_customers | total_orders
--------+--------------------+---------------
 East   |                  3 |             3
 West   |                  2 |             4

In the West region, there are 4 total orders but only 2 unique customers, Riley Nguyen placed 3 of those 4 orders. COUNT(DISTINCT customer_name) and COUNT(*) answer genuinely different questions side by side in the same query: “how many orders” versus “how many distinct customers behind them.”

Try It

1. Write a query for the number of distinct product values that appear anywhere in orders.
2. Write a query showing, for each region, the number of distinct products ordered there.
3. Write a query comparing COUNT(*) and COUNT(DISTINCT customer_name) for the East region only, using WHERE.
4. Explain, in your own words, a real-world question COUNT(DISTINCT column) answers that plain COUNT(*) can’t.

Recap

• COUNT(DISTINCT column) counts unique, non-NULL values, different from COUNT(*) (all rows) and COUNT(column) (non-NULL values, including repeats).
• COUNT(DISTINCT ...) works inside GROUP BY exactly like any other aggregate, computed separately per group.
• Comparing COUNT(*) and COUNT(DISTINCT column) side by side reveals how much repetition exists within a group.

Next lesson: this module’s exercises, building real summary reports with everything covered so far.