COUNT DISTINCT usage and examples

Overview

COUNT(DISTINCT column) ignores NULL and then counts unique values, which answers questions such as how many customers or departments exist. It differs from SELECT DISTINCT: the aggregate returns one number, while SELECT DISTINCT returns the unique detail combinations. Multi-column support varies by database, so complex combinations may require an explicit expression or subquery.

Basic syntax

SELECT COUNT(DISTINCT department) AS dept_count FROM orders;

Examples

COUNT DISTINCT example

Count unique department values in orders so repeated Sales rows contribute only once.

Sample table

Table：orders

orders
department
Sales
IT
Sales
HR

Run result

result
dept_count
3

Common issues

Are COUNT(*) and COUNT(column) the same?

No. COUNT(*) counts rows, while COUNT(column) ignores rows where that column is NULL.

Do MAX and MIN return the rest of the source row?

No. They return aggregate values only; use a join, ordering, or a window function when you need the complete row.

Where should filters go around aggregation?

Put source-row predicates in WHERE and aggregate-result predicates in HAVING.

Keep reading

COUNT SUM AVG GROUP BY HAVING MAX MIN GROUP_CONCAT

COUNT DISTINCT