Level 1 · How many customers came from referrals?

An aggregate function collapses many rows into one value. COUNT is the one you will use most, and it has three forms that answer three different questions:

expression	counts
COUNT(*)	rows, no matter what they contain
COUNT(referral_source)	rows where referral_source is not NULL
COUNT(DISTINCT referral_source)	unique non-NULL values

SELECT
  COUNT(*) AS total_customers,
  COUNT(referral_source) AS with_referral,
  COUNT(DISTINCT referral_source) AS referral_sources
FROM customers;

With no GROUP BY, an aggregate query returns exactly one row: a summary of the whole table (or of whatever rows survive the WHERE).

Why the difference matters

COUNT(column) quietly ignores NULLs. That is useful when you mean it (“how many customers have a source recorded”) and a bug when you do not. Suppose you want the number of orders and write COUNT(coupon_code). You get the number of orders with a coupon, and nothing tells you so.

The safe habits:

• To count rows, use COUNT(*).
• To count “rows that have X”, use COUNT(x) on purpose, and name the alias accordingly.
• To count unique things (customers, products, days), use COUNT(DISTINCT ...).

COUNT(1) is the same as COUNT(*) in every modern database. There is no performance difference, despite what old forum posts say.

Share of rows

A natural follow-up: what percent of customers have a referral source?

SELECT ROUND(100.0 * COUNT(referral_source) / COUNT(*), 1) AS pct_with_referral
FROM customers;

The 100.0 makes the division decimal. In Postgres, COUNT(...) returns an integer and integer / integer truncates, so COUNT(referral_source) / COUNT(*) would give 0. DuckDB, MySQL and BigQuery return a decimal, but writing 100.0 * costs nothing and keeps the query portable.

NULL and DISTINCT

COUNT(DISTINCT referral_source) does not count NULL as a source. SELECT DISTINCT referral_source (from chapter 1) does show a NULL row. So the distinct count is one less than the number of rows DISTINCT returns, if any NULLs exist. Expect this question in interviews.

Common mistakes

• COUNT(DISTINCT *) is not valid. Use COUNT(DISTINCT column), or COUNT(DISTINCT (a, b)) in Postgres and DuckDB for combinations. MySQL accepts COUNT(DISTINCT a, b).
• Counting on a column that is the wrong grain. COUNT(*) on order_items counts lines, not orders. “How many orders” from order lines is COUNT(DISTINCT order_id).