SQL COUNT: COUNT(*) vs COUNT(column) vs COUNT(DISTINCT), and the Traps Interviewers Test

COUNT is simple only after you name what should be counted. Rows, known values, unique entities, matched children, and conditionally selected rows are different units. A query can be syntactically correct and still answer the wrong question by counting at the wrong grain.

The examples below use small PostgreSQL tables to make each choice visible. Predict the output first, especially the zero and duplicate cases.

COUNT(*), COUNT(column), and COUNT(DISTINCT column)

These three expressions inspect the same input differently:

• COUNT(*) counts every row.
• COUNT(user_id) counts rows whose user_id is not NULL.
• COUNT(DISTINCT user_id) removes NULLs and then counts unique remaining values.

Input: events

SELECT
  COUNT(*) AS total_rows,
  COUNT(user_id) AS rows_with_user,
  COUNT(DISTINCT user_id) AS distinct_users
FROM events;

Output

COUNT(DISTINCT user_id) answers a unique-user question only when user_id is the entity key you intend to deduplicate. It does not repair an unclear metric definition. The NULL comparison guide explains why NULL also needs explicit treatment in predicates.

Left joins and zero-safe counts

A LEFT JOIN emits one null-padded row for a customer with no matching order. COUNT(*) sees that row; COUNT(o.order_id) does not because the right-side key is NULL.

Input: customers

Input: orders

SELECT
  c.customer_id,
  c.customer_name,
  COUNT(*) AS joined_rows,
  COUNT(o.order_id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.customer_name
ORDER BY c.customer_id;

Output

Do not turn this into a rule that every outer-join query must count the right table. The correct expression follows the question. If the question is about matched orders, use the order key. If it is literally about joined rows, COUNT(*) describes that unit. The SQL joins guide covers population-preserving joins in more detail.

Conditional counts in one grouped query

Conditional counts let one order population produce several status metrics. PostgreSQL's FILTER syntax keeps each condition beside its aggregate. The final predicate explicitly counts NULL status as missing rather than folding it into another category.

Input: orders

SELECT
  customer_id,
  COUNT(*) AS all_orders,
  COUNT(*) FILTER (WHERE status = 'paid') AS paid_orders,
  COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled_orders,
  COUNT(*) FILTER (WHERE status IS NULL) AS missing_statuses
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

Output

The CASE equivalent is COUNT(CASE WHEN status = 'paid' THEN 1 END). If you write ELSE 0, every row becomes non-null and COUNT counts them all. With SUM(CASE ...), an ELSE 0 is appropriate because the values are added instead of checked for nullness.

Join fanout and the counting grain

A one-to-many join repeats the parent row once per child. First audit the joined grain. The order amount is repeated for every item, so SUM(o.amount) is intentionally labeled as duplicated.

Input: orders

Input: order_items

SELECT
  o.customer_id,
  COUNT(*) AS joined_rows,
  COUNT(DISTINCT o.order_id) AS order_count,
  SUM(o.amount) AS duplicated_order_amount,
  SUM(i.line_amount) AS item_revenue
FROM orders AS o
JOIN order_items AS i
  ON i.order_id = o.order_id
GROUP BY o.customer_id
ORDER BY o.customer_id;

Output

For order revenue and item revenue together, aggregate the child table to one row per order before joining. Now both inputs meet at order grain.

Input: orders

Input: order_items

WITH item_totals AS (
  SELECT order_id, SUM(line_amount) AS item_amount
  FROM order_items
  GROUP BY order_id
)
SELECT
  o.customer_id,
  COUNT(*) AS order_count,
  SUM(o.amount) AS order_revenue,
  SUM(i.item_amount) AS item_revenue
FROM orders AS o
JOIN item_totals AS i
  ON i.order_id = o.order_id
GROUP BY o.customer_id
ORDER BY o.customer_id;

Output

SUM(DISTINCT o.amount) is not a general fix. Two different orders can have the same amount, and one would disappear. Fix the grain, not the symptom. The grain-first approach is developed further in SQL for data analysis, with additional drills in SQL practice questions.

FAQ

Does COUNT(column) count zero or an empty string?

Yes. It skips NULL, not false-like values. Numeric zero and an empty text value are non-null and therefore count.

What does COUNT return when no rows match?

An ungrouped COUNT returns one row containing zero. With GROUP BY, a group that has no input rows does not exist unless another table or generated dimension supplies that group.

Should every unique-user metric use COUNT(DISTINCT user_id)?

Only if the input can contain several rows per user at the point of aggregation. If an earlier stage already guarantees one row per user, COUNT(*) may express the grain more directly. Verify the key rather than adding DISTINCT automatically.

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

Do not choose between them from folklore. They have the same row-counting semantics in PostgreSQL. Actual runtime depends on the table, visibility information, indexes, query predicates, and chosen plan. Use COUNT(*) when you mean rows and inspect a plan when performance matters.