COUNT DISTINCT
beginneraggregation
2 min read
What is COUNT DISTINCT?
COUNT(DISTINCT column) counts the number of unique (non-NULL) values in a column. Unlike COUNT(column), which counts all non-NULL values including duplicates, COUNT(DISTINCT) eliminates duplicates first.
Syntax
SELECT COUNT(DISTINCT column) FROM table_name;When to Use
- Counting unique customers who placed orders
- Finding how many distinct courses have enrollments
- Measuring the cardinality (number of unique values) of a column
Key Points
- Ignores Duplicates — Each unique value is counted once.
- Ignores NULLs — NULL values are not counted.
- Multiple Columns — COUNT(DISTINCT col1, col2) is not standard SQL; use a subquery instead.
- Performance — COUNT(DISTINCT) can be slower on large tables because PostgreSQL must sort or hash the values.
- With GROUP BY — Combine with GROUP BY to count distinct values per group.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
COUNT DISTINCT Challenge
Write a query that solve this task: how many distinct grades exist in the students table?.
Expected result
A single number representing the count of unique grade values (e.g., 5).
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What is COUNT DISTINCT?
Initializing database...Each run starts from fresh sample data.
More Examples
Count distinct students per course
How many unique students are enrolled in each course?
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
Does COUNT(DISTINCT) count NULLs?
No. NULL is not considered a value, so it is excluded from the distinct count.
Can I COUNT(DISTINCT) on multiple columns?
Not directly in standard SQL. Use a subquery: SELECT COUNT(*) FROM (SELECT DISTINCT col1, col2 FROM t) sub;