{"record":{"id":"bd3c08cb6c57c53c","repo":"prestodb/presto","slug":"nested-aggregation","errorCode":"NESTED_AGGREGATION","errorMessage":"Cannot nest aggregations inside aggregation '%s': %s","messagePattern":"Cannot nest aggregations inside aggregation '(.+?)': (.+?)","errorType":"error_code","errorClass":"SemanticException","httpStatus":null,"severity":"error","filePath":"presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/AggregationAnalyzer.java","lineNumber":396,"sourceCode":"                    if (node.getArguments().size() > 1) {\n                        Expression maxStandardErrorExpr = node.getArguments().get(1);\n                        if (maxStandardErrorExpr instanceof DoubleLiteral) {\n                            maxStandardError = ((DoubleLiteral) maxStandardErrorExpr).getValue();\n                        }\n                    }\n                    if (maxStandardError <= lowestMaxStandardError) {\n                        warningCollector.add(new PrestoWarning(PERFORMANCE_WARNING, String.format(\"approx_set can produce low-precision results with the current standard error: %.4f (<=%.4f)\", maxStandardError, lowestMaxStandardError)));\n                    }\n                }\n                if (!node.getWindow().isPresent()) {\n                    List<FunctionCall> aggregateFunctions = extractAggregateFunctions(\n                            analysis.getFunctionHandles(),\n                            node.getArguments(),\n                            functionAndTypeResolver);\n                    List<FunctionCall> windowFunctions = extractWindowFunctions(node.getArguments());\n\n                    if (!aggregateFunctions.isEmpty()) {\n                        throw new SemanticException(NESTED_AGGREGATION,\n                                node,\n                                \"Cannot nest aggregations inside aggregation '%s': %s\",\n                                node.getName(),\n                                aggregateFunctions);\n                    }\n\n                    if (!windowFunctions.isEmpty()) {\n                        throw new SemanticException(NESTED_WINDOW,\n                                node,\n                                \"Cannot nest window functions inside aggregation '%s': %s\",\n                                node.getName(),\n                                windowFunctions);\n                    }\n\n                    if (node.getOrderBy().isPresent()) {\n                        List<Expression> sortKeys = node.getOrderBy().get().getSortItems().stream()\n                                .map(SortItem::getSortKey)\n                                .collect(toImmutableList());","sourceCodeStart":378,"sourceCodeEnd":414,"githubUrl":"https://github.com/prestodb/presto/blob/55bb57d202de3b926896fa966c2c4a44c779634e/presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/AggregationAnalyzer.java#L378-L414","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","solutions":["Compute the inner aggregation in a subquery/CTE and aggregate over its result in the outer query","Replace the nested construct with a single applicable aggregate (e.g. sum(x)/count(*) instead of avg of ratios per group)","If nesting was accidental (e.g. sum(count(x))), drop the redundant outer or inner function","Use GROUPING SETS/ROLLUP instead of manually aggregating aggregated values for totals"],"exampleFix":"// before\nSELECT avg(count(*)) FROM orders GROUP BY customer_id\n// after\nSELECT avg(cnt) FROM (SELECT count(*) AS cnt FROM orders GROUP BY customer_id) t","handlingStrategy":"validation","validationCode":"// reject nested aggregates before executing\nlong aggCount = countAggregateFunctionCalls(expression); // walk the AST\nif (aggCount > 1 && isInsideAggregateCall(expression)) {\n    throw new IllegalArgumentException(\"nested aggregation not allowed\");\n}","typeGuard":null,"tryCatchPattern":"catch (SemanticException e) { if (e.getCode() == NESTED_AGGREGATION) { /* rewrite using subquery: SELECT avg(cnt) FROM (SELECT count(*) cnt ... GROUP BY k) */ } throw e; }","preventionTips":["Never wrap aggregate calls directly in another aggregate","Use CTEs/subqueries for multi-level aggregation","Learn evaluation order: GROUP BY/aggregates then HAVING then window functions","Add SQL lint rules for aggregate-inside-aggregate patterns"],"tags":["sql","aggregation","nested-aggregation","semantic-analysis","presto"],"backgroundTag":"nested-aggregation-not-allowed","analyzedSha":"55bb57d202de3b926896fa966c2c4a44c779634e","analyzedAt":"2026-09-04T12:50:26.162Z","contentChangedAt":"2026-09-04T12:50:26.162Z","schemaVersion":2},"datasetVersion":"2026-09-11T21:17:09.523Z"}