Why does SELECT COUNT(*), SUM(amount) FROM orders WHERE region = 'ZZ' return a row when nothing matches?
answer
- the whole table can be one group
- the group's existence is not data-driven
- one group always emits one row
- empty key set means exactly one group
- adding GROUP BY can empty the result
basics
~20 sWith no GROUP BY, an aggregate query treats the whole filtered input as one implicit group and always returns exactly one row, even if nothing matched. Add GROUP BY and empty input yields zero groups, so zero rows.
solid answer
~50 sAn aggregate query without `GROUP BY` still groups — it just has a single group containing every row that survived `WHERE`. That group exists whether or not it has any rows in it, so the query returns exactly one row, always. Add a grouping key and the rule flips: groups are derived from key values present in the data, so an empty input produces no groups and the query returns *zero* rows. That difference bites in application code. `SELECT COUNT(*) FROM orders WHERE region = 'ZZ'` gives you one row you must read a value from; `SELECT region, COUNT(*) ... GROUP BY region` gives you an empty result set you must handle as "no data". Code that assumes an aggregate query always has a row breaks the moment someone adds a `GROUP BY`, and code that assumes it may be empty misreads a legitimately zero result.
code
sql · 11 lines-- No GROUP BY: exactly one row, whatever the data holds
-- returns (0, NULL) when no 'ZZ' orders exist
SELECT COUNT(*) AS n, SUM(amount) AS total
FROM orders
WHERE region = 'ZZ';
-- With GROUP BY: zero rows when no 'ZZ' orders exist
SELECT region, COUNT(*) AS n, SUM(amount) AS total
FROM orders
WHERE region = 'ZZ'
GROUP BY region;go deeper
Remember the simple rule: an aggregate without GROUP BY always hands back exactly one row, and COUNT(*) in it is 0 when nothing matched. Do not write client code that expects an empty result there.
Explain why: no GROUP BY means an empty grouping-key tuple, hence exactly one group that exists regardless of the data, while GROUP BY derives groups from values that are present. Be able to contrast the two shapes on empty input.
Show that you catch this during refactors. Generalising a single-entity summary into a per-entity one by adding GROUP BY silently changes the result-set contract from always-one-row to possibly-empty, and every caller assuming row zero breaks.
Frame it as an API contract question: query shape determines whether "no data" is signalled by an empty result or by a zero-valued row, and inconsistency across a service's endpoints turns into repeated null-handling bugs in every consumer.
## Two different shapes from one clause SQL has two grouped-query shapes and they behave differently on empty input: ```sql -- shape A: no GROUP BY -- always exactly one row SELECT COUNT(*) AS n, SUM(amount) AS total FROM orders WHERE region = 'ZZ'; -- shape B: with GROUP BY -- zero rows when nothing matches SELECT region, COUNT(*) AS n, SUM(amount) AS total FROM orders WHERE region = 'ZZ' GROUP BY region; ``` Run both against a table that contains no `'ZZ'` orders and shape A returns one row while shape B returns none. ## Why shape A always has a row When a query's `SELECT` list contains an aggregate and there is no `GROUP BY` clause, the whole filtered input is treated as a **single implicit group**. That group is not derived from the data — it is created by the shape of the statement. It exists even when it contains no rows, and one group always emits one row. So the result is a one-row, one-group summary of "everything that got through the filter", including the case where nothing did. This is sometimes phrased as "the table is the group". The grouping key tuple is empty, and there is exactly one empty tuple, so there is exactly one group. ## Why shape B can have no rows With `GROUP BY region`, groups come from the *values present* in the grouping input. No rows means no distinct region values means no groups means no output rows. Grouping never invents a key value that the data does not contain, so there is no `('ZZ', 0, NULL)` row waiting to be emitted. This is the same rule that makes a daily report skip days with no activity: absent keys produce absent rows. ## What is in the one row of shape A The row exists; its values follow the ordinary rules of each aggregate over an empty set of rows. `COUNT(*)` and `COUNT(col)` return `0`, because counting nothing is zero. The other standard aggregates have nothing to combine and produce NULL. That value asymmetry is a property of the aggregate functions rather than of grouping, but it explains why the classic answer row looks like `(0, NULL)` rather than `(0, 0)`. ## Why this matters in application code The two shapes demand different client code: - Shape A: the result set is guaranteed non-empty; read row 1, then decide what a `0`/NULL measure means. - Shape B: the result set may be empty; "no rows" is the signal that no key qualified. A very common defect appears during a refactor. A query that used to summarise a single account (`WHERE account_id = ?`, no `GROUP BY`) is generalised to summarise several accounts (`GROUP BY account_id`). Callers that did `rows[0].total` now hit an empty result set for an account with no activity. The reverse defect is a caller that treats a `0` count as "missing" because it used to receive an empty set. The same asymmetry shows up in scalar subqueries: `(SELECT COUNT(*) FROM ...)` used as an expression is safe precisely because it is guaranteed to produce exactly one row, whereas the same subquery with a `GROUP BY` can produce none — which yields NULL — or several, which is an error. ## Existence checks are a different tool Because of shape A's guarantee, `SELECT COUNT(*) FROM t WHERE ...` is a reliable way to *ask a question* and always get an answer back. That makes it tempting as an existence check, but it is the wrong tool for "is there at least one?": it insists on a full count when you only wanted a yes/no. `EXISTS` expresses the question directly and can stop at the first match. ## Guards you can write If a caller needs shape B's per-key breakdown but also needs a guaranteed row for a key with no data, grouping alone cannot supply it — the key must come from elsewhere, typically a dimension or calendar table left-joined to the grouped result. Conversely, if you want shape A's single row but with a numeric zero instead of NULL, wrap the measure in `COALESCE(...)`. ## Quick summary No `GROUP BY` plus an aggregate means one implicit group and therefore exactly one row, always. `GROUP BY` means groups come from the data, so no data means no rows. Deciding which shape a query has is the first thing to check when a caller complains about "no result" or about "a row full of NULLs".
- What values does that single row carry when the filter matched nothing?COUNT(*) and COUNT(col) return 0 because counting an empty set is zero. SUM, AVG, MIN and MAX have nothing to combine and return NULL. If the caller wants a numeric zero, wrap the measure — for example COALESCE(SUM(amount), 0) — rather than assuming the aggregate does it.
- Why is SELECT COUNT(*) a poor way to ask whether any matching row exists?It works, because the query is guaranteed to return one row, but it asks the engine for a full count when a yes/no answer would do. EXISTS states the question directly and can stop at the first qualifying row, so it is both clearer and cheaper for an existence check.
saying these in an interview costs you the question
- Expects an empty result set from an aggregate with no GROUP BY
- Thinks GROUP BY produces a zero row for keys absent from the data
- Claims COUNT(*) on no matching rows returns NULL
- Assumes adding GROUP BY cannot change how many rows come back
- Says the implicit group only exists when the table is non-empty