COUNT(*) vs COUNT(col) vs COUNT(DISTINCT): rows, values, or people

The campaign report said 4,000 customers. The campaign had reached 1,900 people. Same table, same query, one function. This page covers what the three COUNTs each count, where the 4,000 came from, and the column DISTINCT has to be on. The print-ready A4 PDF is at the bottom.

Three counts

• COUNT(*): rows, NULLs included.
• COUNT(col): rows where col is not NULL.
• COUNT(DISTINCT col): the number of different values of col.

Three functions, three questions. How many rows? How many rows have a value here? How many different values are there?

The report

• COUNT(*) = 4,000: order lines, not customers.
• COUNT(DISTINCT customer_id) = 1,900: people. The real number.
• DISTINCT on the right column: the one that means one person.

Same table, three numbers

SELECT
  COUNT(*)                    AS order_lines,
  COUNT(coupon_code)          AS lines_with_coupon,
  COUNT(DISTINCT customer_id) AS customers
FROM campaign_orders;

-- 4000 | 2600 | 1900

The 4,000 is order lines. The 2,600 is lines where the coupon column is not NULL. The 1,900 is people. The report said customers and showed the first number.

Remember

• COUNT(*) - COUNT(col) is the number of NULLs in col.
• COUNT(1) is the same as COUNT(*), not faster.

The trap: DISTINCT on the wrong column

DISTINCT counts different values of whatever you give it. Give it the column that means one person.

The email count is higher than the id count because fifty people typed their address twice, once with a capital letter. LOWER(TRIM(email)) would bring it closer, but it is still a guess. The id is the column the system uses to mean one person, so it is the column to count.

Say what one row means before you count. One line, one order, one customer: three COUNTs, three numbers, and only one of them answers the question that was asked.

The interview version

"How many customers bought during the campaign?" is a question about people. Before writing the query, say which column identifies a person in this table, then write COUNT(DISTINCT that column ). If the interviewer adds "and how many of them used a coupon", the answer is a COUNT(DISTINCT customer_id) with a WHERE coupon_code IS NOT NULL, not a COUNT(coupon_code), which would count lines again.

The full page is a free print-ready A4 PDF: the three counts, the NULL rule, the right column.