COUNT(*) and COUNT(column) look interchangeable — both start with COUNT, both end up as a number in a report — but they count completely different things. COUNT(*) counts rows. COUNT(column) counts only the rows where that column is not NULL. The moment a column has NULLs — an unresolved support ticket, a rating nobody left, an order that hasn't shipped yet — swapping one for the other silently changes the number, with no error and no warning.
Verified by actually running this against SQLite — not hand-computed. Ten support tickets spread across three agents. resolution_time_minutes is NULL whenever a ticket is still open — that's not missing data, it's the actual, correct state of a ticket nobody has closed yet.
CREATE TABLE tickets(
ticket_id INTEGER,
agent_name TEXT,
customer_id INTEGER,
resolution_time_minutes INTEGER
);
INSERT INTO tickets VALUES
(1,'Meera',501,12),
(2,'Meera',502,8),
(3,'Meera',501,NULL),
(4,'Meera',503,20),
(5,'Rahul',504,15),
(6,'Rahul',504,NULL),
(7,'Rahul',505,NULL),
(8,'Kabir',506,10),
(9,'Kabir',507,25),
(10,'Kabir',506,5);| ticket_id | agent_name | customer_id | resolution_time_minutes |
|---|---|---|---|
| 1 | Meera | 501 | 12 |
| 2 | Meera | 502 | 8 |
| 3 | Meera | 501 | NULL |
| 4 | Meera | 503 | 20 |
| 5 | Rahul | 504 | 15 |
| 6 | Rahul | 504 | NULL |
| 7 | Rahul | 505 | NULL |
| 8 | Kabir | 506 | 10 |
| 9 | Kabir | 507 | 25 |
| 10 | Kabir | 506 | 5 |
Rahul has three tickets, but two of them — 504 (the second time) and 505 — are still open. Meera has one open ticket out of four. Kabir has closed all three of his.
The requirement is simple: "how many tickets has each agent handled?" resolution_time_minutes reads like the column that represents a ticket, so it's the one that gets counted:
SELECT agent_name, COUNT(resolution_time_minutes) AS ticket_count
FROM tickets
GROUP BY agent_name
ORDER BY agent_name;| agent_name | ticket_count |
|---|---|
| Kabir | 3 |
| Meera | 3 |
| Rahul | 1 |
Rahul handled exactly as many tickets as Kabir — three — but the dashboard says one. Two-thirds of his workload disappeared, not because the tickets don't exist, but because they're still open, and an open ticket has no resolution_time_minutes to count.
COUNT(column) is shorthand for "count the rows where this column is not NULL" — the same rule SUM and AVG follow when they quietly skip NULLs instead of treating them as zero. It's not a bug in SQLite or any other engine; it's the standard, documented behavior of COUNT(expr). The bug is choosing a nullable column as a stand-in for "the row exists" when it actually means "this specific thing about the row is known."
SELECT agent_name, COUNT(*) AS ticket_count
FROM tickets
GROUP BY agent_name
ORDER BY agent_name;| agent_name | ticket_count |
|---|---|
| Kabir | 3 |
| Meera | 4 |
| Rahul | 3 |
COUNT(*) counts rows, full stop — it doesn't look inside any column, so a NULL anywhere has no effect on it. Rahul's real count comes back as 3, and Meera's climbs from 3 to 4 once her one open ticket is counted alongside her three closed ones.
There's a third question hiding in the same table: not "how many tickets," but "how many different customers." That's COUNT(DISTINCT customer_id) — and it's worth seeing next to the other two, because it's neither of them.
SELECT agent_name,
COUNT(*) AS total_tickets,
COUNT(resolution_time_minutes) AS resolved_tickets,
COUNT(DISTINCT customer_id) AS unique_customers
FROM tickets
GROUP BY agent_name
ORDER BY agent_name;| agent_name | total_tickets | resolved_tickets | unique_customers |
|---|---|---|---|
| Kabir | 3 | 3 | 2 |
| Meera | 4 | 3 | 3 |
| Rahul | 3 | 1 | 2 |
Meera's 4 tickets came from only 3 unique customers — customer 501 filed twice. Kabir's 3 tickets came from 2 customers, for the same reason. COUNT(DISTINCT customer_id) counts neither rows nor non-NULL values on their own — it counts distinct non-NULL values, which means it still inherits COUNT(column)'s NULL-skipping behavior on top of collapsing duplicates. If a ticket were ever filed with no customer_id at all, that row would be silently left out of this count too, exactly like an open ticket is left out of COUNT(resolution_time_minutes).
If the question is "how many rows/tickets/orders are there" — reach for COUNT(*). It's the only one of the three that's immune to NULLs anywhere in the row.
If the question is "how many rows actually have a value for this specific column" — COUNT(column) is correct, but only when that's genuinely the question. "How many tickets are resolved" is a legitimate use of COUNT(resolution_time_minutes); "how many tickets exist" is not.
If the question is "how many unique values does this column have" — COUNT(DISTINCT column), and remember it drops NULLs before it starts deduplicating, same as its non-distinct sibling.
| Expression | What it counts | Question it actually answers |
|---|---|---|
COUNT(*) | Every row in the group, NULLs included | "How many rows are there, period?" |
COUNT(column) | Rows where column is NOT NULL | "How many rows have a known value for this?" |
COUNT(DISTINCT column) | Distinct NOT NULL values in column | "How many unique values does this have?" |
This one never throws an error either — it just quietly answers a narrower question than the one being asked, and the gap only shows up as "why does this number look too small." Before shipping any COUNT(column), it's worth asking whether NULL in that column means "doesn't count" or just "not filled in yet."