COUNT(*) vs COUNT(DISTINCT): why your two counts disagree, and which one to send

Someone asks how many customers ordered. COUNT(*) and COUNT(DISTINCT buyer_id) both run without an error and give different answers, because they count different things. Here are the first six rows of the orders table in Meridian, a fictional practice marketplace:

On these six rows COUNT(*) is 6, COUNT(DISTINCT buyer_id) is 4 and COUNT(promo_code) is 1. On the whole table:

SELECT COUNT(*), COUNT(DISTINCT buyer_id), COUNT(promo_code)
FROM meridian.orders;
-- result: 9,320 · 3,705 · 2,375

9,320 is orders. 3,705 is people. If the question was "how many customers", the first number is two and a half times too big, and nothing in the query warns you.

What each COUNT counts

COUNT(*) answers "how many rows?". What a row is depends on the table: in meridian.orders one row is one order, in order_items it is one product line. Say the noun before you count. Analysts call this the table's grain.

COUNT(column) is the quiet one. It equals COUNT(*) until the column has NULLs, and then it counts only the filled rows. 6,945 orders have no promo code, so COUNT(promo_code) is 2,375: the orders with a code, not all orders.

COUNT(DISTINCT column) counts different values and ignores NULL. There are two codes, AUTUMN20 and WELCOME5; the empty promo code is not a third one.

Three reasons the counts disagree

1. People come back. 3,652 buyers paid for 9,071 orders. Most of them paid more than once, so rows outnumber people.
2. NULLs drop out. COUNT(column) and COUNT(DISTINCT column) skip empty values; COUNT(*) does not.
3. Joins add rows. Join orders to their product lines and COUNT(*) counts lines. COUNT(DISTINCT …) still counts orders and people:

SELECT COUNT(*), COUNT(DISTINCT o.order_id), COUNT(DISTINCT o.buyer_id)
FROM meridian.orders AS o
JOIN meridian.order_items AS i ON i.order_id = o.order_id
WHERE o.status = 'paid';
-- result: 16,192 · 9,071 · 3,652

16,192 is neither orders nor people. If a total was summed in the same query, it is inflated too; why a JOIN duplicates rows shows what that does to money.

Filter first, then count people

WHERE removes rows before any COUNT sees them. Try it on the six rows above:

SELECT COUNT(*), COUNT(DISTINCT buyer_id)
FROM orders
WHERE status = 'paid';

Distinct counts don't add up

Paid orders came from 3,652 buyers and cancelled orders from 243. Together that is not 3,895: 190 buyers had both, so the table holds 3,705 different buyers.

SELECT status, COUNT(DISTINCT buyer_id) AS buyers
FROM meridian.orders
GROUP BY status;
-- cancelled 243, paid 3,652; together 3,705 people, not 3,895

Adding distinct counts from separate groups counts the overlap twice. The same thing happens when weekly customers are added up into a month: orders vs customers shows it on four weeks of data.

When COUNT(*) is the right answer

When the question is about events: orders, sessions, refunds, page views. "How many orders were paid?" is COUNT(*) with WHERE status = 'paid': 9,071. COUNT(DISTINCT order_id) would return the same number, because each order is already one row.

A rule that works in interviews and at work: name the noun in the question. If one row is one of those nouns, count rows. If not, count distinct ids of that noun.

Send both numbers with their nouns

"3,652 buyers paid for 9,071 orders between 31 August and 22 September" can't be misread. "9,071 customers" will be. Writing the noun next to the number is the cheapest check there is, and it is the one most often skipped.

Practise the question after the SQL screen

"COUNT or COUNT(DISTINCT)?" is a classic first question in a junior analyst SQL screen. The case round that follows asks the harder part: your query runs, can you defend the number? Try the interview question: one question, one tap, the answer, then case 1. It is free.

New to SQL? Start day 1 begins with reading a table before any code. All 10 days of Start are free with an account.

FAQ

What is the difference between COUNT(*) and COUNT(DISTINCT column)?

COUNT(*) counts rows. COUNT(DISTINCT column) counts how many different non-NULL values the column has. On a table of orders, the first gives orders and COUNT(DISTINCT buyer_id) gives buyers: 9,320 and 3,705 on the Meridian practice data.

Does COUNT(column) count NULL values?

No. COUNT(column) counts only the rows where that column is not NULL. COUNT(*) counts every row. The difference between the two is the number of NULLs: COUNT(*) - COUNT(promo_code) gives 6,945 orders without a promo code.

Does COUNT(DISTINCT) count NULL as a value?

No. NULL is ignored, so a column with the values AUTUMN20, WELCOME5 and NULL has COUNT(DISTINCT …) of 2. If "no code" should count as a group, report it separately or replace NULL with a label first.

Is COUNT(1) faster than COUNT(*)?

They return the same number. In common databases any speed difference is negligible, so write the one your team reads more easily. What matters for the result is COUNT(*) versus COUNT(column).

How do I count distinct combinations of two columns?

A portable way is to count the rows of a distinct subquery: SELECT COUNT(*) FROM (SELECT DISTINCT buyer_id, seller_id FROM meridian.orders) AS pairs. Some databases also accept other forms (MySQL has COUNT(DISTINCT a, b)), and they can treat NULLs differently, so check them on your own data first.