Distinct Count

Hook: A distinct count is the SQL version of asking, “How many unique people walked in?” instead of “How many footsteps were heard?” Interviewers love it because it tests whether you understand duplicates and NULL, not just syntax.

Question: What does COUNT(DISTINCT ...) do in SQL?

Answer: It counts the number of unique non-NULL values in a column. Duplicate values are treated as one, so repeated rows do not increase the count. If you need distinct combinations of columns, many databases require a subquery or a dialect-specific form.

Interview-Ready Answer: I use COUNT(DISTINCT column) when I need the number of unique values, not the number of rows. It ignores duplicates and also ignores NULL, so the result is often smaller than COUNT(*). If I need distinct pairs or more complex grouping, I usually switch to a SELECT DISTINCT ... subquery so the logic stays portable across databases.

What a distinct count really means

Detailed Explanation: A distinct count answers: “How many different values exist here?” The database must first look at each row, keep only the values that are not NULL, remove duplicates, and then count what is left. A good mental model is a bouncer at a club: every guest can enter the list once, but repeat arrivals do not get a second wristband.

How it works under the hood

1. The engine reads rows from the table or from the current grouped result.
2. It evaluates the expression inside COUNT(DISTINCT ...). That expression can be a plain column or a computed value like LOWER(email).
3. It drops NULL values because COUNT only counts non-NULL inputs.
4. It de-duplicates the remaining values. Most engines do this with either a hash-based aggregate or a sort-based aggregate. A hash is a lookup structure that remembers what it has seen; a sort groups equal values next to each other so duplicates can be skipped.
5. It returns the number of unique values that survived.
6. If the query is grouped, this whole process happens separately for each group.

COUNT(*) vs COUNT(col) vs COUNT(DISTINCT col)

When and why to use it

• To count unique customers, users, SKUs, error codes, or countries.
• To avoid inflated metrics when the same entity appears many times.
• To measure cardinality, which means the number of unique values in a set.

Performance and practical limits

Distinct counts usually cost more than plain counts because the engine must remember what it has already seen. In a large table with 10 million rows and 200,000 unique values, the engine may keep a large in-memory structure; if memory is too small, it can spill to disk, which is slower. In simple terms, COUNT(*) is usually cheapest, COUNT(col) is close behind, and COUNT(DISTINCT col) is the one that may need real work.

As a rough mental model, the time is usually O(n) on average for hash-based aggregation and can look more like O(n log n) when sorting is needed. Space is proportional to the number of unique values, often written as O(k), where k is the distinct cardinality. That is why a column with 50 unique values is easy, while a column with millions of unique values can be expensive.

Important edge cases

• NULL values are ignored, so a column with only NULLs returns 0.
• Text comparison can depend on collation, which is the database’s rule for comparing strings. In some systems, 'A' and 'a' may be treated as the same; in others, they are different.
• If you want distinct normalized values, count the expression you care about, such as COUNT(DISTINCT LOWER(email)).
• Distinct across multiple columns is not portable in one universal syntax, so a subquery with SELECT DISTINCT is often the safest answer.

Memory Hook: “One wristband per guest.” If you have already counted that person, the bouncer does not hand out another wristband just because they came back in line.

Real-World Story: Imagine a checkout service for an e-commerce site. The product team wants to know how many unique customers placed an order today, not how many order rows were written. One customer may retry payment, and one order can generate multiple events, so a plain row count would overstate demand. The correct query uses COUNT(DISTINCT customer_id) so the dashboard reflects real people, not repeated attempts.

What goes wrong when someone misunderstands this? The analytics dashboard suddenly shows a huge spike in customers after a payment incident, even though traffic did not increase. Logs reveal repeated inserts for the same customer_id, support tickets mention “double counted sales,” and the business team starts making bad staffing or inventory decisions. The symptom is not a crash; it is a misleading number that quietly breaks trust.

Follow-up & Tricky Questions:

• How do you count distinct values across two columns? Many databases support a dialect-specific form, but the most portable answer is SELECT COUNT(*) FROM (SELECT DISTINCT col1, col2 FROM t) x. That makes the deduplication step explicit and easy to explain.
• Why is COUNT(DISTINCT ...) slower than COUNT(*)? Because the engine must track uniqueness, not just increment a counter. That means extra memory, and sometimes a sort or a spill to disk if the distinct set is large.
• What happens when the column contains only NULLs? The result is 0, because COUNT ignores NULL values entirely. This is a very common interview trap.
• Is COUNT(DISTINCT col) the same as SELECT DISTINCT col? No. SELECT DISTINCT returns the unique values themselves, while COUNT(DISTINCT ...) returns just the number of those unique values.
• Can I use it on an expression like LOWER(email)? Yes, and that is often the right move when you want normalized uniqueness. You are counting the distinct result of the expression, not the raw stored text.
• Does case matter for strings? It depends on the database collation. In case-insensitive collations, 'A' and 'a' may collapse to one value; in case-sensitive collations, they may count separately.
• Does COUNT(DISTINCT 1) always return 1? If the query has at least one row, yes, because the expression is the same non-NULL constant for every row. If there are no rows, the result is 0.

Tricky / gotcha questions:

• Can you count distinct values and still keep all rows? Not with a plain aggregate alone. You usually need a window-friendly workaround, a subquery, or a separate join depending on the database.
• Does COUNT(DISTINCT col) count one NULL as a unique value? No, NULL is excluded from the count entirely.
• If two rows look different but compare equal after trimming or lowercasing, how many are counted? The count is based on the expression you supply, so COUNT(DISTINCT TRIM(LOWER(name))) can collapse values that look different in storage but equal after normalization.

Common Mistakes:

• Using COUNT(*) when the question asks for unique values. COUNT(*) counts rows, not distinct entities; switch to COUNT(DISTINCT ...) when duplicates exist.
• Forgetting that NULL is ignored. If the answer seems too small, check whether missing values should be handled with COALESCE or a different rule.
• Assuming multi-column distinct syntax is universal. It is not; the portable solution is a SELECT DISTINCT subquery.
• Ignoring collation and normalization. If business logic says Alice@example.com and alice@example.com are the same, count the normalized form.

Memory Hook: “One wristband per guest.” Duplicate arrivals do not get extra wristbands, and NULL never gets into the club.

Cheat Sheet:

• COUNT(DISTINCT col) = unique non-NULL values.
• COUNT(*) = every row.
• COUNT(col) = non-NULL rows.
• Distinct counts often use hash or sort aggregation.
• High cardinality can be memory-heavy and may spill to disk.
• For multiple columns, a SELECT DISTINCT subquery is the safest portable pattern.

Practice Tasks:

• Create a table with repeated product IDs and verify the difference between COUNT(*) and COUNT(DISTINCT product_id).
• Add a few NULL values and confirm that the distinct count does not include them.
• Build a SELECT DISTINCT subquery for two columns and compare its result to the single-column count.