After grouping orders by both region and payment method, what identifies each row of the collapsed result, and how many rows are there?

## Group identity stops being a value and becomes a pair The grouped operation is one pass that gives every row a key, runs a computation once per key, and reassembles the answers into a result. It treats the **grouping key** - the column, columns or derived expression whose value decides which rows belong together - as a single thing, however many columns went into it. Name one column and a group's identity is one value. Name two and the identity is a **pair**, and two records land in the same group only when they agree on **both**. That is the whole of the conceptual change. The split still records which rows belong to which key, the per-key computation still runs once per key, and collapsing to one row per group still returns exactly as many rows as there are groups. What moves is how fine the groups are, what labels the result carries, and therefore how many rows come back. ## The row count has a ceiling and a reality Take a table of orders whose region column holds six distinct values and whose payment method column holds four. Grouped on the pair and collapsed, the result holds one row for each pair that **actually occurs together in the data**: | quantity | example value | where it comes from | |---|---|---| | distinct values of the first key | 6 | the data in that column | | distinct values of the second key | 4 | the data in that column | | pairs the two columns could form | 24 | the product of the two counts | | pairs some record actually carries | 11 | counted from the data, never from the columns | | rows in the collapsed result | 11 | one per pair that occurred | The product is a **ceiling**, not a prediction. Real key columns are rarely independent of one another: a payment method may only be offered in some regions, a product line may only exist in some markets. The gap between 24 and 11 is the ordinary case, and it widens fast as keys are added - three keys of 20, 50 and 12 distinct values have a ceiling of 12,000 and may well produce a few hundred rows. ## Predicting the result before you run it This is the habit an interviewer is probing, because it is the cheapest correctness check available: 1. **Name the identity out loud.** Say what one output row stands for: "one region-and-payment-method combination", not "one region". 2. **Compute the ceiling.** Multiply the distinct-value counts of the keys. A result above that number is impossible, and means a key is not the column you think it is. 3. **Expect to land well under it.** A count that lands exactly on the ceiling means the data is dense. That is possible, and it is worth confirming rather than assuming. 4. **Compare against the single-key run.** Grouping on the first key alone gives 6 rows; adding a second key can only refine those 6 into more rows, never into fewer. A count far above the ceiling you computed is the classic signal that a key column is dirtier than you believed. Trailing spaces, mixed casing and two spellings of one value each count as a distinct value, and each of those multiplies through the pair count. ## What the numbers do, and what they do not do - Each output number is still **the same reduction over the records sharing that identity**. Adding a key does not change the arithmetic of the reduction; it changes which records are in the pot. - For a reduction that folds through a group - a sum, a count, a minimum - the total across the whole result is unchanged by adding a key, because every record still belongs to exactly one group. Six regional sums and eleven region-and-method sums add up to the same grand figure. - For a reduction that does not fold that way, such as a mean, the finer result **cannot** be collapsed back by averaging its rows. A mean of means equals the overall mean only when the groups are the same size, which is exactly what grouping on a second key stops being true. - Nothing about a second key makes a record count twice. A record carries exactly one pair of key values and contributes to exactly one group. ## Where the pair ends up The pair has to appear in the result somehow, and here the family genuinely differs. Designs that carry a row-label slot compose the two values into one label made of two parts, one part per key, and put the aggregated columns beside it. Designs with no row-label concept return the two keys as two more ordinary output columns. Both carry the same identity and the same numbers; only the second form can be addressed by column name in a later step, which is worth establishing about your tool before you write that step.