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

Q: What is the difference between COUNT(*), COUNT(col), and COUNT(DISTINCT col)?

**Target Audience:** Beginners who have written `COUNT(*)` and want to know why a report's numbers disagree; candidates preparing for the classic COUNT question. **Difficulty:** Medium **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16. MySQL's multi-column form is cited from its manual. > **How to read this doc:** Each idea is explained in plain language first, then tagged with `📌 Interview term:` so you learn the words an interviewer expects. Every result shown was produced by actually running the query; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First A museum has a visitor book at the door. At closing time three people ask three questions: - The cleaner: "How many lines were written in the book today?" **8.** - The membership officer: "How many lines include a membership number?" Some visitors left it blank: **5.** - The director: "How many **different** members

The problem

Question presented to candidate: "On our visits table, COUNT(*), COUNT(user_id) and COUNT(DISTINCT user_id) give three different numbers. Explain what each one counts, and tell me which one you would use for 'visits per user' in a report that LEFT JOINs users to visits."

What a strong answer should cover:

COUNT(*) counts rows, whatever they contain; COUNT(col) counts rows where col is not NULL; COUNT(DISTINCT col) counts distinct non-NULL values
COUNT(1) and COUNT(*) are the same thing; neither is faster in a meaningful way
The LEFT JOIN trap: COUNT(*) counts the NULL-padded row, so a user with no visits shows 1; count a column of the right-hand table instead
Counting distinct combinations: standard SQL has no multi-column COUNT(DISTINCT a, b); use a subquery with SELECT DISTINCT a, b, a row value in PostgreSQL, or MySQL's extension; beware the concatenation trick and NULLs
Cost: COUNT(DISTINCT) needs a sort or hash of all values; COUNT(*) on PostgreSQL still scans the table because of MVCC

Code / implementation expected: Yes — the three counts side by side, and the LEFT JOIN per-user count.

Schema used by the examples:

count

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners who have written COUNT(*) and want to know why a report's numbers disagree; candidates preparing for the classic COUNT question. Difficulty: Medium Engines: SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16. MySQL's multi-column form is cited from its manual.

How to read this doc: Each idea is explained in plain language first, then tagged with 📌 Interview term: so you learn the words an interviewer expects. Every result shown was produced by actually running the query; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

A museum has a visitor book at the door. At closing time three people ask three questions:

The cleaner: "How many lines were written in the book today?" 8.
The membership officer: "How many lines include a memb

The solution is hidden so you can try first

11 min read · 11 sections

Run the code

Edit the query and run it against the tables for this question.

SQLThe three counts (and COUNT(1)) side by side on two columns

SQLThe LEFT JOIN trap: COUNT(*) gives users with no visits a count of 1

SQLCounting distinct combinations, and counting with a condition

PostgreSQL: COUNT(DISTINCT (a, b)) on a row value

SELECT COUNT(DISTINCT (referrer, device)) AS row_pairs,
       COUNT((referrer, device)) AS row_count,
       COUNT(*) FILTER (WHERE referrer IS NULL) AS no_referrer
FROM visits;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.