Python SQL COUNT: COUNT(*), COUNT(column) & COUNT(DISTINCT)
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.
"`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
Common Beginner Traps & How to Fix Them
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.
Live Interactive Example
Hit Run Code to see it liveYour Turn: Micro Challenge
No pressure! Edit the starter code below and test your solution with instant feedback.
Count Total and Distinct Records
- Select `COUNT(*) AS total`, `COUNT(DISTINCT category) AS unique_cats` from `products`.
Finished reading and practicing?
Mark this lesson as completed to update your course progress.