{"record":{"id":"ccaab5223f60a21b","repo":"prestodb/presto","slug":"must-be-aggregate-or-group-by","errorCode":"MUST_BE_AGGREGATE_OR_GROUP_BY","errorMessage":"'%s' must be an aggregate expression or appear in GROUP BY clause","messagePattern":"'(.+?)' must be an aggregate expression or appear in GROUP BY clause","errorType":"error_code","errorClass":"SemanticException","httpStatus":null,"severity":"error","filePath":"presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/AggregationAnalyzer.java","lineNumber":204,"sourceCode":"\n        this.groupingFields = groupByExpressions.stream()\n                .map(NodeRef::of)\n                .filter(columnReferences::containsKey)\n                .map(columnReferences::get)\n                .flatMap(Collection::stream)\n                .collect(toImmutableSet());\n\n        this.groupingFields.forEach(fieldId -> {\n            checkState(isFieldFromScope(fieldId, sourceScope),\n                    \"Grouping field %s should originate from %s\", fieldId, sourceScope.getRelationType());\n        });\n    }\n\n    private void analyze(Expression expression)\n    {\n        Visitor visitor = new Visitor();\n        if (!visitor.process(expression, null)) {\n            throw new SemanticException(MUST_BE_AGGREGATE_OR_GROUP_BY, expression, \"'%s' must be an aggregate expression or appear in GROUP BY clause\", expression);\n        }\n    }\n\n    /**\n     * visitor returns true if all expressions are constant with respect to the group.\n     */\n    private class Visitor\n            extends AstVisitor<Boolean, Void>\n    {\n        @Override\n        protected Boolean visitExpression(Expression node, Void context)\n        {\n            throw new UnsupportedOperationException(\"aggregation analysis not yet implemented for: \" + node.getClass().getName());\n        }\n\n        @Override\n        protected Boolean visitAtTimeZone(AtTimeZone node, Void context)\n        {","sourceCodeStart":186,"sourceCodeEnd":222,"githubUrl":"https://github.com/prestodb/presto/blob/55bb57d202de3b926896fa966c2c4a44c779634e/presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/AggregationAnalyzer.java#L186-L222","documentation":"Semantic error from AggregationAnalyzer.analyze: an expression in a SELECT/HAVING/ORDER BY (in an aggregated query context) references a column that is neither inside an aggregate function nor part of the GROUP BY clause, so its value is undefined per group. Presto rejects it with MUST_BE_AGGREGATE_OR_GROUP_BY.","triggerScenarios":"Running a query like SELECT a, count(*) FROM t GROUP BY b, or ORDER BY a when a is not grouped and not aggregated; verified via verifySourceAggregations/verifyOrderByAggregations.","commonSituations":"Forgetting a column in GROUP BY; using an alias or ordinal mismatch between SELECT and GROUP BY; adding ORDER BY columns after grouping; hand-edited queries.","solutions":["Add the offending column to the GROUP BY clause","Wrap the column in an aggregate function, e.g. max(a) or any_value(a)","Remove the column/expression from SELECT or ORDER BY if unneeded","If you meant the whole-row key, group by all non-aggregated selected expressions"],"exampleFix":"// before\nSELECT customer_id, region, count(*) FROM orders GROUP BY customer_id\n// after\nSELECT customer_id, region, count(*) FROM orders GROUP BY customer_id, region","handlingStrategy":"validation","validationCode":"// before running: check every non-aggregated SELECT/ORDER BY column is in GROUP BY\nSet<String> groupBy = Set.of(\"customer_id\", \"region\");\nList<String> selected = List.of(\"customer_id\", \"region\");\nif (!groupBy.containsAll(selected)) throw new IllegalArgumentException(\"column missing from GROUP BY\");","typeGuard":null,"tryCatchPattern":"catch (SemanticException e) { if (e.getCode() == MUST_BE_AGGREGATE_OR_GROUP_BY) { /* add the reported expression to GROUP BY or aggregate it, then retry */ } throw e; }","preventionTips":["Keep GROUP BY and SELECT column lists in sync","Prefer grouping by full expressions, not aliases","Review ORDER BY columns whenever you add GROUP BY","Use EXPLAIN/analyze early to catch semantic errors before production"],"tags":["sql","aggregation","group-by","semantic-analysis","presto"],"backgroundTag":"column-not-in-group-by","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"}