A grouped count over a table of orders returns 11 rows though the business defines 14 categories — why?

## What a grouped result is built from The **grouped operation** — one pass that gives every row a key, runs a computation once per key, and reassembles the answers into a result — has exactly one source for the rows it emits: the **grouping key** values it met while scanning the input. The key expression is evaluated for each row, rows carrying equal values are collected together, the per-key computation runs, and one output row comes back per key that was formed. A key value that no row carried was never formed, so nothing was emitted for it. The result therefore answers *what occurred*, not *what could occur*. Eleven rows out of a fourteen-category business means eleven distinct category values appeared in this input. The list of categories the business recognises lives somewhere else entirely — in a reference table, in a specification, in somebody's head — and the grouped operation never consulted it, because nothing handed it to the operation. ## Why this is not a defect Nothing failed, and "the grouping dropped three categories" is the wrong diagnosis: there were no rows to drop. Keeping four situations apart is most of the skill here, because they have different causes and different repairs: - **A declared category with no rows** — the value is legitimate but this input contains none of it. That is the case in the question. - **A combination that never occurred** — with two keys, a pair of values that no single row carried; it disappears the same silent way. - **A row whose grouping key has no value** — the row exists but carries no key, so it either forms a group of its own or leaves the computation entirely. - **A group left with no rows by an earlier step** — the rows were removed upstream and the key value went with them, so by the time the split ran there was nothing to key. Only the first two are about categories that never reached the result; the third is about rows, and the fourth happened before the grouped operation ran at all. ## The two conditions for an empty category to appear A result row for a category with no rows is not something a tool can invent out of the data. Two things must hold, and they are independent: 1. **The key column must carry a declared set of allowed values** — a list, attached to the column itself, of every value the column is permitted to take. Without that declaration the column is only the values it happens to hold, and a value that is not there is indistinguishable from a value that does not exist. 2. **The tool must emit the unobserved members of that set.** Carrying the declaration is not the same as emitting from it. Some designs emit every declared value, observed or not; some emit only what occurred even when the declaration is present; several expose it as a setting on the grouped operation. A design with no notion of a declared set cannot do it at all. Say the assumption out loud rather than guessing: *"if the key column declares its allowed values and the tool emits the unobserved ones, I get fourteen rows; otherwise I get eleven."* That sentence is the whole answer, and it is answerable without knowing which tool is in front of you. ## A missing row and a zero row are not the same downstream | What the consumer does | With the category missing | With an explicit empty row | |---|---|---| | A person reads the table | the category is not on screen, and its absence is easy to overlook | the category is visible and its emptiness is stated | | A step reads the result by position | later rows move up into positions that meant something else | positions stay stable from run to run | | Two runs are lined up side by side | the two results have different shapes and the comparison has to cope | both runs have the same shape | | A grand total is taken | unchanged — nothing was contributed either way | unchanged, for the same reason | The last row is the one candidates get wrong in both directions. A category with no rows changes **no total**; what it changes is the **shape** of the result, and everything downstream that depends on the shape. That is why the question is asked at all: the arithmetic is fine and the report is still misleading. ## What the interviewer is listening for Three things, in order. First, that you did not call it a bug — you can state the mechanism that produced eleven rows. Second, that you know a zero row is possible but conditional, and can name both conditions instead of asserting that some tool "always" fills the gap. Third, that you treat the hole as a finding rather than a cosmetic issue: before shipping the result you decide, deliberately, whether the consumer needs the complete set of categories or only the ones that occurred, and you make that shape explicit rather than inheriting whichever one the default gave you.