COUNT looks like the simplest function in SQL, and it’s a favourite warm-up question because the two common forms answer different questions. count(*) answers “how many rows are there?” count(phone) answers “how many rows have a phone number?” They agree only when the column has no NULLs, which is exactly the situation that hides bugs in testing and breaks in production.
Every result here was produced on PostgreSQL 16.15. The behaviour is standard SQL, so SQLite, MySQL and SQL Server return the same counts.
The three forms side by side
A small customers table, with some missing phone numbers and referrals:
CREATE TABLE customers (id int PRIMARY KEY, name text, referred_by int, phone text);
INSERT INTO customers VALUES
(1, 'Ana', NULL, '555-0101'),
(2, 'Ben', 1, NULL),
(3, 'Chen', 1, '555-0103'),
(4, 'Dee', NULL, NULL),
(5, 'Eli', 3, '555-0103');
SELECT count(*) AS all_rows,
count(phone) AS with_phone,
count(DISTINCT phone) AS distinct_phones,
count(referred_by) AS referred,
count(DISTINCT referred_by) AS referrers
FROM customers; all_rows | with_phone | distinct_phones | referred | referrers
----------+------------+-----------------+----------+-----------
5 | 3 | 2 | 3 | 2Read each number as an answer to a question:
- 5 customers exist.
- 3 customers have a phone on file. Ben and Dee have NULL, so they’re skipped.
- 2 different phone numbers are in use, because Chen and Eli share one. NULL isn’t counted as a distinct value.
- 3 customers were referred by someone.
- 2 distinct people (Ana and Chen) made referrals.
The rule underneath all of it: aggregate functions other than count(*) skip NULL inputs. count(*) has no input expression, so there’s nothing to be NULL; it just counts rows.
What about count(1) and count(id)?
count(1) counts rows where the constant 1 isn’t NULL, which is every row, so it returns the same as count(*). The idea that count(1) is faster is a myth carried over from very old databases. In PostgreSQL, count(*) is, if anything, the cheaper of the two because it doesn’t evaluate an argument per row. Use count(*) when you mean rows; it states the intent.
count(id) on a NOT NULL primary key also equals count(*) on that table alone. It stops being equal the moment that table is the nullable side of an outer join, which is the next section and the bug interviewers most want you to spot.
The LEFT JOIN count that says 1 instead of 0
“List every customer with their number of orders, including customers who never ordered.” Orders:
CREATE TABLE orders (id int PRIMARY KEY, customer_id int, status text);
INSERT INTO orders VALUES (10, 1, 'paid'), (11, 1, 'refunded'), (12, 3, 'paid'), (13, NULL, 'paid');
SELECT c.name, count(*) AS star, count(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id; name | star | orders
------+------+--------
Ana | 2 | 2
Ben | 1 | 0
Chen | 1 | 1
Dee | 1 | 0
Eli | 1 | 0Ben has never ordered, yet count(*) says 1. A LEFT JOIN keeps Ben by producing one row where every orders column is NULL. That row is real as far as count(*) is concerned. count(o.id) looks at o.id, sees NULL, and correctly reports 0.
The fix is always “count a column from the optional table that can’t be NULL in a real match”. The primary key is the safest choice. Counting o.status would also work today, but it would start undercounting the day someone allows orders without a status.
PostgreSQL also lets you write count(o), which counts the whole row from o and returns the same 0/1/2 as above, because an unmatched row is NULL as a whole. It’s a neat trick, but count(o.id) is clearer to readers coming from other databases.
Conditional counts with FILTER
Reports often want several counts at once: total orders, paid, refunded. You don’t need three queries or a CASE inside SUM:
SELECT count(*) AS orders,
count(*) FILTER (WHERE status = 'paid') AS paid,
count(*) FILTER (WHERE status = 'refunded') AS refunded,
count(customer_id) AS with_customer
FROM orders; orders | paid | refunded | with_customer
--------+------+----------+---------------
4 | 3 | 1 | 3FILTER is standard SQL and supported by PostgreSQL and SQLite. In MySQL or SQL Server you’d write sum(CASE WHEN status = 'paid' THEN 1 ELSE 0 END), which does the same job. Note with_customer is 3: order 13 has no customer, a data quality issue that count(customer_id) surfaces and count(*) hides.
DISTINCT and the NULL-swallowing concatenation
A common way to count distinct combinations is to glue columns together. It quietly goes wrong when any part is NULL, since || with a NULL operand yields NULL, and NULLs aren’t counted:
SELECT count(DISTINCT referred_by::text || phone) AS concat
FROM customers; -- 2Five customers have five different (referred_by, phone) combinations, but three of them contain a NULL, so the concatenation drops them and you get 2. Gluing without a separator has a second bug: (1, '23') and (12, '3') both become '123'. If you need distinct pairs, count from a subquery: SELECT count(*) FROM (SELECT DISTINCT referred_by, phone FROM customers) s, which returns 5 and treats NULLs as equal for grouping purposes.
Empty inputs: zero rows versus a zero
count over no rows returns 0, while sum over no rows returns NULL:
SELECT count(*) FROM customers WHERE false; -- 0
SELECT sum(id) FROM customers WHERE false; -- NULLWrap sums in coalesce(sum(x), 0) when the result feeds arithmetic. There’s one more twist: add a GROUP BY and an empty input produces no rows at all, not a row with 0. SELECT count(*) FROM customers WHERE false GROUP BY referred_by returns zero rows, because there are no groups to report on. Dashboards that expect “0” for a quiet day need a calendar or category table to LEFT JOIN from.
Why count(*) can be slow on big PostgreSQL tables
Interviewers sometimes ask why SELECT count(*) FROM events takes seconds on a 200-million-row table when MySQL’s MyISAM used to return instantly. PostgreSQL keeps no running row count, because under MVCC different transactions can legitimately see different numbers of rows. It has to scan (often with parallel workers, or an index-only scan if the visibility map is current). If an approximate count is fine, SELECT reltuples FROM pg_class WHERE relname = 'events' returns the planner’s estimate instantly. If an exact count is needed often, maintain it in a separate counter table and accept the write contention that brings. The MVCC and vacuum guide explains why visibility is per transaction.
Interview practice: the support dashboard
A support team wants, per agent: total tickets assigned, tickets resolved, and number of distinct customers helped. Tickets have agent_id, customer_id (nullable for anonymous chats) and resolved_at (NULL while open). Agents with no tickets must still appear with zeros. Write the query and explain each count.
Answer
SELECT a.id, a.name,
count(t.id) AS assigned,
count(t.id) FILTER (WHERE t.resolved_at IS NOT NULL) AS resolved,
count(DISTINCT t.customer_id) AS customers_helped
FROM agents a
LEFT JOIN tickets t ON t.agent_id = a.id
GROUP BY a.id, a.name;LEFT JOIN from agents keeps agents with no tickets. count(t.id) rather than count(*) makes those agents show 0 instead of 1. resolved could be count(t.resolved_at), which counts non-NULL timestamps, but the FILTER version says what it means and survives a schema change. count(DISTINCT t.customer_id) ignores anonymous chats, which is probably right for “customers helped”, but it’s a product decision worth confirming: if anonymous chats should count, they’d need a separate count(t.id) FILTER (WHERE t.customer_id IS NULL) column, since there’s no way to tell anonymous visitors apart.