prestodb/presto · error · SemanticException

NESTED_AGGREGATION

NESTED_AGGREGATION

Error message

Cannot nest aggregations inside aggregation '%s': %s

What it means

AggregationAnalyzer.visitFunctionCall rejects aggregate functions nested inside other aggregate functions (e.g. sum(count(x))), because aggregation of an already aggregated value is undefined in a single pass. Throws NESTED_AGGREGATION with the outer function name and the list of detected inner aggregate calls.

Source

Thrown at presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/AggregationAnalyzer.java:396

                    if (node.getArguments().size() > 1) {
                        Expression maxStandardErrorExpr = node.getArguments().get(1);
                        if (maxStandardErrorExpr instanceof DoubleLiteral) {
                            maxStandardError = ((DoubleLiteral) maxStandardErrorExpr).getValue();
                        }
                    }
                    if (maxStandardError <= lowestMaxStandardError) {
                        warningCollector.add(new PrestoWarning(PERFORMANCE_WARNING, String.format("approx_set can produce low-precision results with the current standard error: %.4f (<=%.4f)", maxStandardError, lowestMaxStandardError)));
                    }
                }
                if (!node.getWindow().isPresent()) {
                    List<FunctionCall> aggregateFunctions = extractAggregateFunctions(
                            analysis.getFunctionHandles(),
                            node.getArguments(),
                            functionAndTypeResolver);
                    List<FunctionCall> windowFunctions = extractWindowFunctions(node.getArguments());

                    if (!aggregateFunctions.isEmpty()) {
                        throw new SemanticException(NESTED_AGGREGATION,
                                node,
                                "Cannot nest aggregations inside aggregation '%s': %s",
                                node.getName(),
                                aggregateFunctions);
                    }

                    if (!windowFunctions.isEmpty()) {
                        throw new SemanticException(NESTED_WINDOW,
                                node,
                                "Cannot nest window functions inside aggregation '%s': %s",
                                node.getName(),
                                windowFunctions);
                    }

                    if (node.getOrderBy().isPresent()) {
                        List<Expression> sortKeys = node.getOrderBy().get().getSortItems().stream()
                                .map(SortItem::getSortKey)
                                .collect(toImmutableList());

View on GitHub (pinned to 55bb57d202)

Solutions

  1. Compute the inner aggregation in a subquery/CTE and aggregate over its result in the outer query
  2. Replace the nested construct with a single applicable aggregate (e.g. sum(x)/count(*) instead of avg of ratios per group)
  3. If nesting was accidental (e.g. sum(count(x))), drop the redundant outer or inner function
  4. Use GROUPING SETS/ROLLUP instead of manually aggregating aggregated values for totals

Example fix

// before
SELECT avg(count(*)) FROM orders GROUP BY customer_id
// after
SELECT avg(cnt) FROM (SELECT count(*) AS cnt FROM orders GROUP BY customer_id) t
Defensive patterns

Strategy: validation

Validate before calling

// reject nested aggregates before executing
long aggCount = countAggregateFunctionCalls(expression); // walk the AST
if (aggCount > 1 && isInsideAggregateCall(expression)) {
    throw new IllegalArgumentException("nested aggregation not allowed");
}

Try / catch

catch (SemanticException e) { if (e.getCode() == NESTED_AGGREGATION) { /* rewrite using subquery: SELECT avg(cnt) FROM (SELECT count(*) cnt ... GROUP BY k) */ } throw e; }

Prevention

When it happens

Trigger: Writing queries like SELECT sum(count(*)) FROM t GROUP BY k, or AVG(sum(x)) / MAX(avg(y)), where extractAggregates finds aggregate FunctionCalls among the outer aggregate's arguments.

Common situations: Trying to average a per-group count (usually wants AVG over a subquery); copy-pasted expressions stacking aggregates; template-generated SQL that appends another aggregate wrapper.

Related errors

AI-assisted analysis of prestodb/presto@55bb57d202 (2026-09-04). Data as JSON: /api/errors/bd3c08cb6c57c53c. Report an issue: GitHub.