What does SELECT COUNT(*), SUM(total) FROM orders return when orders is empty?

## One group by default When a query's `SELECT` list contains aggregates and there is no `GROUP BY`, SQL treats the entire input — however many rows survived `FROM` and `WHERE` — as **one group**. Grouping produces one output row per group, so such a query returns exactly one row. That is true for a million rows, for one row, and for zero rows. The result set never has two rows and never has none. This is the property that makes `SELECT COUNT(*) FROM t` usable as a scalar: the caller can fetch one row without checking whether the result set is empty. ## What the single row contains on empty input ```sql -- No row satisfies the filter SELECT COUNT(*) AS n, -- 0 SUM(total) AS s, -- NULL MIN(total) AS lo, -- NULL MAX(total) AS hi, -- NULL AVG(total) AS av -- NULL FROM orders WHERE status = 'SHIPPED'; ``` `COUNT` is the outlier, and deliberately so: the number of things in an empty collection is exactly 0, a precise answer. For `SUM`, `AVG`, `MIN` and `MAX` there is no value to name, so the standard specifies NULL. Note that `COUNT(total)` also returns 0 here, since counting the non-NULL values of an empty input is still zero. ## Add GROUP BY and the shape changes ```sql SELECT status, COUNT(*) FROM orders WHERE status = 'SHIPPED' GROUP BY status; -- zero rows ``` Grouping derives its groups **from the data**. No rows means no distinct `status` values, so no groups, so no output rows. There is no phantom group with a NULL key and a count of zero. This asymmetry — implicit single group always produces a row, explicit grouping produces a row only per existing group — is the crux of the question and the part candidates most often get backwards. A `HAVING` clause can remove the implicit group too: ```sql SELECT COUNT(*) FROM orders WHERE 1 = 0 HAVING COUNT(*) > 0; -- zero rows ``` The single group is formed, the predicate evaluates to false, and the row is discarded. ## Why it matters in practice **Application contracts.** Code that does `resultSet.next()` once and reads column 1 works for the ungrouped form and breaks on the grouped form when data is absent. Conversely, code written against the grouped form handles zero rows naturally. Knowing which shape you produced determines which client-side code is correct. **Scalar subqueries.** `(SELECT SUM(total) FROM orders WHERE customer_id = c.id)` used in a `SELECT` list is safe from the "more than one row" error precisely because the ungrouped aggregate always returns exactly one row — but it returns NULL for customers with no orders, which then propagates through any surrounding arithmetic. This is why such subqueries are almost always written inside a `COALESCE`. **Health-check queries.** `SELECT COUNT(*) FROM critical_table` returning `0` proves the query ran and the table is empty. A grouped query returning nothing is ambiguous to a naive caller: no data, or did the job fail? Prefer the ungrouped form for assertions of this kind. **Reporting gaps.** If you need a row for a category that has no rows — "0 shipped orders today" — grouping cannot invent it. You must supply the categories from somewhere: a `LEFT JOIN` from a list of statuses (or a calendar table) to the data, so the driving side guarantees the row and the aggregate fills in `COUNT(*)` from the joined column. That is the standard technique for zero-filling a report. ## Saying it crisply "No `GROUP BY` means one implicit group, so exactly one row comes back even from an empty table: `COUNT(*)` is 0, and `SUM`, `AVG`, `MIN`, `MAX` are all NULL. Add `GROUP BY` and you get one row per group that actually exists, so an empty input gives zero rows. If I need a zero row for an absent category, I `LEFT JOIN` the categories to the data rather than expecting `GROUP BY` to produce it."