COUNT — How Many?
"How many customers do we have?" "How many orders shipped this week?" "How many customers never gave us an email?" Counting is the first question almost every report asks. SQL answers all three with COUNT — but in three slightly different forms, and mixing them up gives believable wrong numbers.
The rule
COUNT(*) counts rows; COUNT(column) counts values that are not NULL.
COUNT is an aggregate function: it takes many rows in and gives one number out. Our customers table:
| customer_id | name | city | |
|---|---|---|---|
| 1 | Asha | Pune | asha@example.com |
| 2 | Ravi | Delhi | NULL |
| 3 | Meera | Pune | meera@example.com |
| 4 | Kabir | Mumbai | kabir@example.com |
SELECT COUNT(*) FROM customers; -- 4: every row
SELECT COUNT(email) FROM customers; -- 3: Ravi's NULL is skipped
SELECT COUNT(DISTINCT city) FROM customers; -- 3: Pune, Delhi, MumbaiCOUNT(*)counts rows, whatever they contain.COUNT(column)counts rows where that column is not NULL.COUNT(DISTINCT column)counts the different non-NULL values.
WHERE runs before COUNT, so it narrows the rows first: SELECT COUNT(*) FROM customers WHERE city = 'Pune'; gives 2. And when no rows match at all, COUNT returns 0, not NULL — it is the one aggregate that always gives a number.
The shortcut
Translate the English question into one of three shapes:
- "How many rows / things?" →
COUNT(*) - "How many have a value?" →
COUNT(column) - "How many different values?" →
COUNT(DISTINCT column)
Look at the question
"How many different customers appear in orders?" The word different is the clue.
Drills
Use the customers table above and this orders table. Answer fast — trust the rule.
| order_id | customer_id | product_id | quantity | status |
|---|---|---|---|---|
| 101 | 1 | 1 | 2 | shipped |
| 102 | 2 | 3 | 1 | pending |
| 103 | 1 | 2 | 5 | shipped |
| 104 | 3 | 1 | 1 | cancelled |
| 105 | 3 | 2 | 3 | shipped |
What does SELECT COUNT(email) FROM customers; return?
What does SELECT COUNT(*) FROM orders WHERE status = 'shipped'; return?
Which query answers "how many different products have been ordered"?
What does SELECT COUNT(*) FROM orders WHERE status = 'returned'; return when no order has that status?
Remember
- COUNT(*) counts rows; COUNT(column) counts values that are not NULL.
- Rows →
*, "have a" → column, "different" → DISTINCT. - WHERE filters before counting, and COUNT of nothing is 0, never NULL.