COUNT and DISTINCT

Something new happens in this lesson. Every query so far has returned one output row per input row. COUNT is the first thing that collapses a hundred rows into one, and that idea, called aggregation, carries the next four lessons.

It is also the fastest way to understand a file you have just been handed. How many rows? How many products? Is the date column one row per day, or several? Four short queries and you know more about a file than an hour of scrolling would tell you.

COUNT(*) counts rows

SELECT COUNT(*) FROM csv returns a single row with a single number. The star here does not mean "all columns" the way it does in a plain SELECT; it means "rows, regardless of what is in them". A row of entirely missing values still counts.

Put a WHERE in front of it and you count the matching rows instead, which turns COUNT into the cheapest question in SQL: how many of these are there? It is worth reaching for before you look at any rows at all. If you expect a couple of hundred western sales and the count says three, you have learned something before you have read a single value.

COUNT(column) counts values

COUNT(satisfaction) is not the same thing. Naming a column tells COUNT to count the rows where that column is not null. On a file with no gaps the two numbers agree, which is why the distinction is easy to miss for months and then bites hard.

The gap between them is the useful part. Run SELECT COUNT(*) AS rows, COUNT(score) AS scored and the difference is exactly the number of missing scores, computed without a single IS NULL. You will use precisely that shape on the support-ticket file in lesson 9, where twenty of the ninety tickets have no satisfaction rating.

DISTINCT lists the values

SELECT DISTINCT channel FROM csv returns each channel once. Three rows, on this file. This is the single most useful query for getting to know a category column, and it is how you discover that your data contains both "Online" and "online", or a value with a trailing space that has been silently failing your filters all afternoon.

DISTINCT applies to the whole select list, not to one column. SELECT DISTINCT region, channel gives you each combination that actually occurs, which is a different and often more interesting question. There is no such thing as making one column distinct and leaving another alone; if you want that, you want GROUP BY.

NULL counts as a value for DISTINCT, and all nulls are treated as the same one, so a column with gaps produces one extra row in the list. That is inconsistent with how NULL behaves everywhere else in SQL, and it is a deliberate exception in the standard rather than a quirk of this engine.

COUNT(DISTINCT column)

Put them together and you get the number of unique values: COUNT(DISTINCT product). Unlike plain DISTINCT, this one ignores nulls, in line with COUNT's usual behavior.

Comparing this against the row count is how you check a claimed key. If a file is supposed to have one row per day and COUNT(DISTINCT date) comes back lower than COUNT(*), you have duplicates and every total you compute from that file will be wrong. Two numbers, five seconds, and it has caught more broken exports for me than any validation tool.

One practical note for later: counting distinct values exactly is expensive on very large tables, because the engine has to remember every value it has seen. Warehouses offer approximate versions such as approx_count_distinct, accurate to a percent or so and vastly cheaper. On a hundred rows it makes no difference at all.

Naming the number

An aggregate without an alias comes back with a header like count_star(). Alias it. Three of the four exercises below check your column names for exactly this reason: a number nobody can read the label of is not an answer, it is a trivia question.

What to look up next

Read about HyperLogLog, the algorithm behind every approximate distinct count in every warehouse. It is a genuinely beautiful piece of engineering: it counts billions of unique values in a few kilobytes of memory, by paying attention to nothing but the longest run of leading zeros it has seen.