The aggregate functions overview introduced COUNT(*) and COUNT(column) to answer "how many rows / how many non-NULL values." Beacon Robotics' HR team has a sharper version of that question: "how many different departments do we actually have?" not how many employees, how many distinct department names. That's what this lesson adds.
Learning Objectives
- Recap COUNT(*) vs COUNT(column) from the aggregate functions overview.
- Use COUNT(DISTINCT column) to count unique values.
- Scope COUNT() with WHERE.
- Know that COUNT(*) and COUNT(1) are functionally identical.
Rows or Values
Depending on the form used
COUNT()
*, column, or DISTINCT column
A Single Number
How many
Quick Recap: COUNT(*) vs. COUNT(column)
Covered in full in the aggregate functions overview: COUNT(*) counts every row, COUNT(column) counts only rows where that column isn't NULL.
SELECT COUNT(*) AS total_rows,
COUNT(middle_name) AS rows_with_middle_name
FROM employees;COUNT(DISTINCT column): Counting Unique Values
This answers a different question than either form above: not how many employees, not how many have a department value, but how many distinct department values exist at all. Three employees in Engineering and two in Research still counts as 2, not 5. DISTINCT inside COUNT() works identically on all six databases in this course.
SELECT COUNT(DISTINCT department) AS department_count FROM employees;
COUNT() with WHERE
Same order of operations as every aggregate function: WHERE filters first, COUNT() counts whatever rows remain.
SELECT COUNT(*) AS active_engineering_employees FROM employees WHERE department = 'Engineering' AND status = 'active';
Try It Yourself
Write a query that returns how many distinct departments have at least one active employee.
SELECT COUNT(DISTINCT department) AS active_department_count FROM employees WHERE status = 'active';
Frequently Asked Questions
Can DISTINCT be combined with COUNT(*)?
No, COUNT(DISTINCT *) isn't valid syntax, DISTINCT inside COUNT() needs a specific column (or in some databases, a list of columns) to compare for uniqueness. * has no single column to deduplicate on.
Does COUNT(DISTINCT column) count NULL as a distinct value?
No. NULL is excluded from COUNT(DISTINCT column) the same way it's excluded from plain COUNT(column), consistent with how every aggregate function treats NULL.
Common Beginner Mistakes
Using COUNT(column) when COUNT(DISTINCT column) was meant
COUNT(department) counts how many rows have a non-NULL department, which is almost always just the row count. COUNT(DISTINCT department) counts how many different department values exist. These answer genuinely different questions and are easy to mix up.
Assuming COUNT(*) is slower than COUNT(1) or a specific column
A long-standing myth. On every database in this course, modern query optimizers treat COUNT(*), COUNT(1), and COUNT(primary_key_column) as effectively identical in performance, COUNT(*) is not slower. It's also the clearest to read, since it obviously means "count the rows."
Best Practices
- Use COUNT(*) when the goal is simply counting rows, it's the clearest way to express that intent.
- Use COUNT(DISTINCT column) specifically when the goal is counting unique values, not rows.
- Don't choose COUNT(1) over COUNT(*) for performance reasons, there isn't one on any modern database.
Interview Questions
What's the difference between COUNT(*) and COUNT(1)?
Functionally, nothing, both count every row in the result set, and every modern database optimizer executes them identically. COUNT(1) is a holdover habit from older database engines where it was sometimes marginally faster; on MySQL, MariaDB, PostgreSQL, SQLite, SQL Server, and Oracle today, there's no performance difference, so COUNT(*) is preferred for clarity.
Write a query that returns the number of distinct departments among employees hired after 2020-01-01.
SELECT COUNT(DISTINCT department) AS department_count FROM employees WHERE hire_date > '2020-01-01'; — WHERE filters to the relevant rows first, then COUNT(DISTINCT department) counts unique department values among just those.
Summary
Beyond COUNT(*) and COUNT(column) from the overview, COUNT(DISTINCT column) counts unique values rather than rows, and COUNT(*) and COUNT(1) are performance-identical on every database in this course, despite a common myth otherwise.
What's Next?
The next lesson covers SUM(), totaling a numeric column, including SUM(DISTINCT column) and a NULL-related quirk that catches many beginners off guard.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.