prestodb/presto · error · SemanticException

MUST_BE_AGGREGATE_OR_GROUP_BY

MUST_BE_AGGREGATE_OR_GROUP_BY

Error message

'%s' must be an aggregate expression or appear in GROUP BY clause

What it means

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.

Source

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

        this.groupingFields = groupByExpressions.stream()
                .map(NodeRef::of)
                .filter(columnReferences::containsKey)
                .map(columnReferences::get)
                .flatMap(Collection::stream)
                .collect(toImmutableSet());

        this.groupingFields.forEach(fieldId -> {
            checkState(isFieldFromScope(fieldId, sourceScope),
                    "Grouping field %s should originate from %s", fieldId, sourceScope.getRelationType());
        });
    }

    private void analyze(Expression expression)
    {
        Visitor visitor = new Visitor();
        if (!visitor.process(expression, null)) {
            throw new SemanticException(MUST_BE_AGGREGATE_OR_GROUP_BY, expression, "'%s' must be an aggregate expression or appear in GROUP BY clause", expression);
        }
    }

    /**
     * visitor returns true if all expressions are constant with respect to the group.
     */
    private class Visitor
            extends AstVisitor<Boolean, Void>
    {
        @Override
        protected Boolean visitExpression(Expression node, Void context)
        {
            throw new UnsupportedOperationException("aggregation analysis not yet implemented for: " + node.getClass().getName());
        }

        @Override
        protected Boolean visitAtTimeZone(AtTimeZone node, Void context)
        {

View on GitHub (pinned to 55bb57d202)

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

Example fix

// before
SELECT customer_id, region, count(*) FROM orders GROUP BY customer_id
// after
SELECT customer_id, region, count(*) FROM orders GROUP BY customer_id, region

Defensive patterns

Strategy: validation

Validate before calling

// before running: check every non-aggregated SELECT/ORDER BY column is in GROUP BY
Set<String> groupBy = Set.of("customer_id", "region");
List<String> selected = List.of("customer_id", "region");
if (!groupBy.containsAll(selected)) throw new IllegalArgumentException("column missing from GROUP BY");

Try / catch

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; }

Prevention

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

When it happens

Trigger: 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.

Common situations: 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.

Related errors

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