Lesson 23 of 61

5 mins read

SQL COUNT: COUNT(*), COUNT(column) & COUNT(DISTINCT)

In Plain English

The `COUNT()` function tallies row occurrences. Crucially, `COUNT(*)` counts total rows in the table (including rows with NULLs), `COUNT(column_name)` counts only rows where that specific column is NOT NULL, and `COUNT(DISTINCT column_name)` counts unique non-null values.

The Mental Model

"`COUNT(*)` is counting total chairs occupied in a room; `COUNT(email)` is counting only chairs where the person has an email address; `COUNT(DISTINCT city)` is counting how many different hometowns are represented."

Deep Dive: How It Works

COUNT(*) vs COUNT(col): `COUNT(*)` is optimized by databases to count matching rows regardless of column values. `COUNT(col)` evaluates nullability per row.

COUNT(DISTINCT col): Computes unique cardinality: `SELECT COUNT(DISTINCT country) FROM users`.

Conditional Counting with CASE: `COUNT(CASE WHEN status = 'active' THEN 1 END)` counts matching subsets within a single pass.

Syntax Blueprint

SELECT
  COUNT(*) AS total_rows,
  COUNT(phone) AS rows_with_phone,
  COUNT(DISTINCT department) AS unique_departments
FROM staff;

Use COUNT(*) for total row count and COUNT(col) for non-null counts.

Core Rules to Remember

COUNT(*) is Standard: COUNT(*) is safe, fast, and does not require scanning column nullability.

Null Omission in COUNT(col): Useful for determining completeness ratios (e.g. % of users with verified phones).

COUNT(DISTINCT): Calculates the number of unique entries in a dataset.

Common Beginner Traps & How to Fix Them

Mistake:Using `COUNT(column)` expecting total rows when the column contains NULL values.

Why it happens: COUNT(column) silently skips rows with NULL in that column.

How to fix: Use `COUNT(*)` to count total records.

Pro Tip: Use `COUNT(CASE WHEN score >= 50 THEN 1 END) AS passed_count` to count conditional subsets without extra queries.

Live Interactive Example

Hit Run Code to see it live

Comparing COUNT(*), COUNT(col), and COUNT(DISTINCT)

Python 3.12

Output Console

Click "Run Code" to view the rendered output.

How it works: COUNT(*) returned 5 total rows, COUNT(phone) skipped 2 nulls returning 3, and COUNT(DISTINCT role) returned 2 unique roles.

Your Turn: Micro Challenge

No pressure! Edit the starter code below and test your solution with instant feedback.

Micro Exercise

Count Total and Distinct Records

Select `COUNT(*) AS total`, `COUNT(DISTINCT category) AS unique_cats` from `products`.

Sandbox Output

Click "Run & Check" to test your solution.

Finished reading and practicing?

Mark this lesson as completed to update your course progress.