COUNT Function
Count rows, count values, count unique values — and know the difference
Intermediate 12 minCOUNTaggregate
Counting Rows and Values
COUNT is the most used aggregate function — but it comes in three very different forms, and confusing them is one of the most common analytics mistakes. Here's the critical difference:
COUNT(*) counts all rows — Including rows with NULLs. It answers "how many rows exist?" and is the only aggregate function that includes NULL values in its count. Use this for total row counts.
COUNT(column) skips NULLs — Counts only rows where that specific column is NOT NULL. If a table has 100 rows but 5 have NULL in the email column,
COUNT(email) returns 95, not 100.COUNT(DISTINCT column) counts unique values — Returns the number of unique non-NULL values. 100 rows with 10 distinct countries gives you 10. Useful for understanding cardinality and data distribution at a glance.
Knowledge Canvas
How COUNT Works
Count rows or non-NULL values
- COUNT(*) counts ALL rows including NULLs
- COUNT(col) counts only non-NULL values in that column
- COUNT(DISTINCT col) counts unique non-NULL values
- Returns 0 for empty sets — never NULL
NULL Behavior
Critical COUNT + NULL rules
- COUNT(*) = 10 but COUNT(email) = 8 means 2 NULLs in email
- COUNT(DISTINCT NULL) = 0
- COUNT(*) with LEFT JOIN counts unmatched rows too
- Use COUNT(right_table.id) after LEFT JOIN for accurate counts
COUNT Mistakes
Frequent errors
- COUNT(col) in LEFT JOIN → 0 for unmatched, not NULL
- COUNT(*) vs COUNT(1) → identical performance
- Forgetting DISTINCT: COUNT(country) ≠ COUNT(DISTINCT country)
- COUNT in HAVING requires GROUP BY first
Result Behavior
What COUNT returns
- Always returns an integer ≥ 0
- Never returns NULL — empty set = 0
- With GROUP BY: one count per group
- Without GROUP BY: single row result
Syntax
Syntax Template
1-- Count all rows (including NULLs)
2SELECT
3 COUNT(*)
4FROM table;
5
6-- Count non-NULL values in a column
7SELECT
8 COUNT(column)
9FROM table;
10
11-- Count unique non-NULL values
12SELECT
13 COUNT(DISTINCT column)
14FROM table;
COUNT(*)Counts ALL rows (including those with NULLs)COUNT(column)Counts rows where column is NOT NULLCOUNT(DISTINCT col)Counts unique non-NULL values Sample Data
reviews
12 rows
| id | product_id | rating | comment |
|---|---|---|---|
| 7001 | P101 | 5 | Excellent mouse, very comfy |
| 7002 | P101 | 4 | NULL |
| 7003 | P101 | 3 | Good but battery drains fast |
| 7004 | P102 | 5 | Solid build, worth every rupee |
| 7005 | P102 | 4 | Spacious — fits two monitors |
| 7006 | P103 | 4 | NULL |
| 7007 | P104 | 5 | Just works, durable braided cable |
| 7008 | P105 | 5 | Great sound for the price |
| 7009 | P105 | 4 | Battery could be better |
| 7010 | P107 | 3 | NULL |
| 7011 | P108 | 5 | Good notebooks, smooth paper |
| 7012 | P109 | 5 | Premium feel, noise cancellation is real |
customers
12 rows
| id | name | city | country | |
|---|---|---|---|---|
| C1001 | Aarav Sharma | [email protected] | Mumbai | India |
| C1002 | Sara Chen | [email protected] | Singapore | Singapore |
| C1003 | James Wilson | [email protected] | London | UK |
| C1004 | Maria Garcia | [email protected] | Madrid | Spain |
| C1005 | Yuki Tanaka | [email protected] | Tokyo | Japan |
| C1006 | Priya Patel | [email protected] | Delhi | India |
| C1007 | Alex Johnson | [email protected] | New York | USA |
| C1008 | Chen Wei | [email protected] | Shanghai | China |
| C1009 | Emma Brown | [email protected] | Sydney | Australia |
| C1010 | Omar Hassan | [email protected] | Dubai | UAE |
| C1011 | Lena Muller | [email protected] | Berlin | Germany |
| C1012 | Ravi Kumar | [email protected] | Bangalore | India |
Worked Example
Compare COUNT(*), COUNT(comment), and COUNT(DISTINCT rating) on the reviews table.
SQL
1SELECT
2 COUNT(*) AS total_reviews,
3 COUNT(comment) AS with_comments,
4 COUNT(DISTINCT rating) AS unique_ratings
5FROM reviews;
COUNT(*) = 12 (all rows). COUNT(comment) = 9 (three reviews have NULL comments). COUNT(DISTINCT rating) = 3 (ratings are 3, 4, and 5).
Output
1 row
| total_reviews | with_comments | unique_ratings |
|---|---|---|
| 12 | 9 | 3 |
Key Concepts
1COUNT(*) counts all rows including those with NULLs — COUNT(column) counts only non-NULL values in that column
2COUNT(DISTINCT col) counts unique non-NULL values
3Mixing up the three COUNT forms causes silent miscounts
4COUNT(*) with GROUP BY counts per group, not the whole table
Pro Tip
Counting is the foundation of data analysis. Knowing the difference between the three COUNT forms prevents silent miscounts.
When to Use
Total orders, active users, products per category, unique visitors, finding tables with missing data.
How many customers are there in total? How many unique countries do they come from? Show both in one query.
Your Query