aggregate function calls cannot be nested" fix

TL;DR

Compute the inner aggregate in a subquery (or CTE), then apply the outer aggregate in the wrapping query. SQL engines evaluate all aggregates in a single GROUP BY pass, so they refuse to nest them; splitting the query into two levels gives each aggregate its own grouping pass.

The query

"aggregate function calls cannot be nested" fix

Use this when

• The error names nested or containing aggregate function calls
• You wrote something like SUM(COUNT()), AVG(SUM(x)), or MAX(COUNT())
• You need per-group values rolled up to a grand total or average

Not for

• Window function frame clause errors
• Aggregations that run but return wrong numbers (check GROUP BY keys)
• Aggregations that are just slow (indexing or statistics issue)

Steps

1. Reproduce with a minimal query to confirm nesting is the problem

SELECT SUM(COUNT(*)) FROM orders GROUP BY customer_id;

Expected output: the nested-aggregate error. The inner COUNT(*) groups by customer, the outer SUM wants to group those groups, and the engine cant do both in one pass.

2. Move the inner aggregate into a subquery, the outer aggregate outside

SELECT SUM(order_count) AS total_orders
FROM (SELECT COUNT(*) AS order_count FROM orders GROUP BY customer_id) s;

Expected output: one row with the grand total. The subquery produces one row per customer; the outer query aggregates those rows.

3. Use a CTE when the inner query is complex or reused

Same idea, more readable:

WITH per_customer AS (
  SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS revenue
  FROM orders
  GROUP BY customer_id
)
SELECT AVG(order_count) AS avg_orders_per_customer,
       SUM(revenue) AS total_revenue
FROM per_customer;

Expected output: one row with both rollups. Each aggregate now has exactly one GROUP BY level to work with.

4. For the inner value and the rollup side by side, use a window function

Window functions run after GROUP BY, so an aggregate inside a window over grouped rows is legal:

SELECT customer_id,
       COUNT(*) AS order_count,
       SUM(COUNT(*)) OVER () AS total_orders
FROM orders
GROUP BY customer_id;

Expected output: one row per customer with its own count plus the grand total repeated.

5. Double-check the grouping grain still means what you intended

SELECT COUNT(*) AS groups, SUM(order_count) AS total
FROM (SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id) s;

Expected output: groups equals the number of distinct customers and total equals the raw order count. If the numbers look off, the subquery is grouping at the wrong grain, not the outer query.

Variant phrasings

aggregate functions cannot contain aggregate functions

SQL Server and MySQL word it this way. Same cause, same subquery fix.

how to sum a count in SQL

The two-level pattern from step 2. Count per group inside, sum outside.

nested aggregation not allowed

Some engines phrase the error this way. If you see it on a window function, check whether the window sits on top of an aggregate (legal) or inside one (not legal).

Why it happens

Aggregate functions collapse a group of rows into one value during the GROUP BY phase, and there is only one such phase per query level. A nested aggregate would need two grouping phases at once: one to compute the inner values per group, another to roll those values up. SQL resolves this by making you write the two phases as two query levels, which is what the subquery or CTE does.

Edge cases

• STRING_AGG of a COUNT has the same restriction; the two-level rewrite applies to every aggregate.
• Filtering before the inner aggregate (WHERE) versus after (HAVING on the outer level) changes results; put row filters inside the subquery.
• DISTINCT inside nested aggregates (SUM(COUNT(DISTINCT x))) is doubly nested; split it the same way.
• Some BI tools generate nested aggregates silently; if the generated SQL fails, wrap it using the CTE pattern from step 3.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_AjrBDX59ZHoxlkhetwYUcw

Resolved from the public thread: https://vectle.com/posts/pst_AjrBDX59ZHoxlkhetwYUcw

Thread query: "aggregate function calls cannot be nested" fix

TL;DR: Compute the inner aggregate in a subquery (or CTE), then apply the outer aggregate in the wrapping query. SQL engines evaluate all aggregates in a single GROUP BY pass, so they refuse to nest them; splitting the query into two levels gives each aggregate its own grouping pass.