Introduction

Using DISTINCT and COUNT together in MySQL is a standard way to count unique values rather than total rows. The core pattern is simple, but confusion appears when people mix up COUNT(DISTINCT column) with SELECT DISTINCT ... and when they forget how NULL values are handled. Once those differences are clear, the query shape becomes straightforward.

Count Distinct Values in One Column

The standard pattern is:

SELECT COUNT(DISTINCT email) AS unique_emails
FROM users;

This counts the number of unique non-NULL email values in the table.

That is different from:

SELECT COUNT(email)
FROM users;

The second query counts all non-NULL email rows, including duplicates.

COUNT(DISTINCT ...) versus SELECT DISTINCT

These two queries answer different questions.

SELECT DISTINCT email
FROM users;

This returns the distinct values themselves.

SELECT COUNT(DISTINCT email)
FROM users;

This returns how many distinct values there are.

That distinction matters because people often write SELECT DISTINCT email, COUNT(*) ... when what they really wanted was a single scalar count of unique values.

Count Distinct Combinations of Columns

MySQL also supports counting unique combinations of more than one column.

SELECT COUNT(DISTINCT first_name, last_name) AS unique_name_pairs
FROM customers;

This counts distinct (first_name, last_name) combinations, not distinct values in each column independently.

That is useful when uniqueness is defined by a pair such as city and postal code, or first name and last name together.

NULL Handling Matters

COUNT(DISTINCT column) ignores NULL values. So if a column contains several NULLs, they do not count as one distinct value in the result.

For example, if email contains:

• '[email protected]'
• '[email protected]'
• '[email protected]'
• 'NULL'
• 'NULL'

then:

SELECT COUNT(DISTINCT email) FROM users;

returns 2, not 3.

If NULL should count as a category, you need to coalesce it explicitly.

SELECT COUNT(DISTINCT COALESCE(email, 'NO_EMAIL'))
FROM users;

This changes the semantics intentionally.

Grouped Distinct Counts

You can also combine grouped queries with distinct counting.

SELECT department_id, COUNT(DISTINCT employee_id) AS unique_employees
FROM attendance
GROUP BY department_id;

This counts unique employees per department instead of across the whole table.

The pattern is common in reporting because it separates total row counts from unique-entity counts.

Watch Performance on Large Tables

Distinct counting may require sorting, hashing, or deduplication work, depending on the query plan and indexes. On large tables, it can be noticeably more expensive than plain COUNT(*).

Useful practical rules:

• index the column when distinct counting is common
• avoid unnecessary expressions inside DISTINCT unless needed
• check the execution plan if the query becomes slow

The syntax is easy. The cost depends on data size and indexing.

Use the Right Query for the Real Question

A few common questions map to different SQL:

• "How many rows are there?" means COUNT(*)
• "How many rows have a non-null email?" means COUNT(email)
• "How many unique email values are there?" means COUNT(DISTINCT email)
• "What are the unique email values?" means SELECT DISTINCT email

Confusing these is one of the main reasons distinct-count queries go wrong.

Common Pitfalls

• Using SELECT DISTINCT ... when the real goal is only the count of unique values.
• Forgetting that COUNT(DISTINCT column) ignores NULL.
• Assuming distinct counts on multiple columns behave like separate per-column counts.
• Reaching for COUNT(DISTINCT ...) without thinking about index support on large tables.
• Mixing grouped aggregation and distinct counting without being clear about which level of uniqueness matters.

Summary

• 'COUNT(DISTINCT column) counts unique non-NULL values in that column.'
• 'SELECT DISTINCT column returns the unique values themselves, not the count.'
• MySQL can count distinct combinations across multiple columns.
• 'NULL values are ignored unless you explicitly convert them with COALESCE or a similar expression.'
• For large tables, distinct counts can be more expensive than plain counts, so indexing and query shape matter.