Why does SELECT MAX(COUNT(*)) FROM orders GROUP BY customer_id fail, and how do you rewrite it?

## The error and what it really means This looks reasonable and is rejected: ```sql SELECT MAX(COUNT(*)) FROM orders GROUP BY customer_id; ``` The intent is clear — "how many orders does the busiest customer have?" — but the statement asks for **two levels of aggregation while declaring one**. Work through the pipeline. `GROUP BY customer_id` partitions the rows into one group per customer. `COUNT(*)` reduces each group to a single number. At that point the query has produced one row per customer, and grouping is finished. `MAX` needs a *set* of values to reduce, but no clause in this query says which set that is or how it should be grouped, so the statement has no meaning to give. Standard SQL states the rule directly: an aggregate function's argument may not contain another aggregate function evaluated at the same query level. Engines surface it with messages such as *aggregate function calls cannot be nested* or *cannot use an aggregate or a subquery in an expression used for the group by list*. ## The portable rewrite Make the second level a real query level. A derived table is the canonical form and works everywhere: ```sql SELECT MAX(order_count) AS busiest FROM (SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id) AS per_customer; ``` The alias on the derived table is not decoration — several engines require every derived table to be named. A `WITH` clause expresses the same thing and usually reads better when there are two or three such steps: ```sql WITH per_customer AS ( SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id ) SELECT MAX(order_count) FROM per_customer; ``` If you also want to know *which* customer, do not reach back for a nested aggregate — order the grouped result and take the top row: ```sql SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id ORDER BY order_count DESC FETCH FIRST 1 ROW ONLY; ``` That returns one customer even when several tie; if ties must all appear, you need an explicit tie-handling construct rather than a row limit. ## What the rule does *not* forbid Three neighbouring shapes are legal, and mixing them up is the usual source of confusion. **Ordinary functions wrapping an aggregate are fine.** `ROUND(AVG(price), 2)`, `COALESCE(SUM(amount), 0)` and `CAST(COUNT(*) AS DECIMAL(10,2))` all take a single already-computed value as input. The prohibition is specifically aggregate-inside-aggregate. **Arithmetic between aggregates is fine.** `SUM(revenue) / COUNT(*)` and `MAX(price) - MIN(price)` combine two values that were each computed over the same group — one level, two aggregates, no nesting. **Aggregates inside an aggregate's argument expression are fine when only the outer one is an aggregate.** `SUM(price * quantity)` aggregates an expression over rows; nothing is nested. ## A dialect caveat worth mentioning At least one major engine relaxes this. Oracle permits a single level of nesting when a `GROUP BY` is present, so `SELECT MAX(COUNT(*)) FROM orders GROUP BY customer_id` runs there and returns the largest per-customer count. Treat that as a portability note, not a technique: the derived-table form runs everywhere, expresses the two levels explicitly, and extends naturally when someone later asks for the second-busiest customer or for the average of the counts. If you name the exception in an interview, say the rest of the sentence too — "…and I would still write the subquery". ## Why interviewers like this question It is a compact test of whether you understand that aggregation is a *level*, not just a function call. A candidate who says "you can't nest aggregates" has memorised a rule; a candidate who says "COUNT already collapsed each group, so MAX has nothing left to consume — I need a second query level" has the model. The second answer also predicts, without being told, that the same fix applies to `AVG(COUNT(*))`, `SUM(MAX(x))` and every other pair.