vitessio/vitess · error

group by expression is not allowed to reference an aggregate

Error message

group by expression is not allowed to reference an aggregate expression: %v

What it means

A GROUP BY column must reference a plain (non-aggregate) select-list expression. If the group-by alias resolves to a count/sum item (operation != opExpr), the builder rejects it because grouping by an aggregate is meaningless for the materializer's insertIgnore plan.

Source

Thrown at go/vt/vttablet/tabletmanager/vreplication/table_plan_builder.go:564

	})
}

func (tpb *tablePlanBuilder) analyzeGroupBy(groupBy *sqlparser.GroupBy) error {
	if groupBy == nil {
		// If there's no grouping, the it's an insertNormal.
		return nil
	}
	for _, expr := range groupBy.Exprs {
		colname, ok := expr.(*sqlparser.ColName)
		if !ok {
			return fmt.Errorf("unsupported non-column name or alias in group by clause: %v", sqlparser.String(expr))
		}
		cexpr := tpb.findCol(colname.Name)
		if cexpr == nil {
			return fmt.Errorf("group by expression does not reference an alias in the select list: %v", sqlparser.String(expr))
		}
		if cexpr.operation != opExpr {
			return fmt.Errorf("group by expression is not allowed to reference an aggregate expression: %v", sqlparser.String(expr))
		}
		cexpr.isGrouped = true
	}
	// If all colExprs are grouped, then it's an insertIgnore.
	tpb.onInsert = insertIgnore
	for _, cExpr := range tpb.colExprs {
		if !cExpr.isGrouped {
			// If some colExprs are not grouped, then it's an insertOnDup.
			tpb.onInsert = insertOnDup
			break
		}
	}
	return nil
}

func (tpb *tablePlanBuilder) getPKColsInfo(uniqueKeyColumns []string, colInfos []*ColumnInfo) (pkColsInfo []*ColumnInfo) {
	if len(uniqueKeyColumns) == 0 {
		// No PK override

View on GitHub (pinned to 01a25a7d17)

Solutions

1. Remove the aggregate from the GROUP BY clause; only group by base columns selected in the list.
2. Group by the underlying column and aggregate separately: SELECT k, count(*) AS c FROM t GROUP BY k.
3. If aggregate-of-aggregates is needed, materialize first and run a second query over the materialized table.

Example fix

// before
select count(*) as c from t group by c
// after
select k, count(*) as c from t group by k

// Require GROUP BY items to reference non-aggregate select expressions
func groupByRefsPlainCols(sel *sqlparser.Select) bool {
    for _, e := range sel.GroupBy.Exprs {
        c, ok := e.(*sqlparser.ColName)
        if !ok { return false }
        for _, se := range sel.SelectExprs {
            ae, ok := se.(*sqlparser.AliasedExpr)
            if !ok { continue }
            if ae.As.String() == c.Name.String() {
                switch ae.Expr.(type) {
                case *sqlparser.CountStar, *sqlparser.Sum:
                    return false
                }
            }
        }
    }
    return true
}

func isAggregateExpr(n sqlparser.SQLNode) bool { switch n.(type) { case *sqlparser.CountStar, *sqlparser.Sum: return true }; return false }

When it happens

Trigger: Materialize/vreplication query like SELECT count(*) AS c FROM t GROUP BY c; analyzeGroupBy finds cexpr with operation opCount/opSum instead of opExpr.

Common situations: Users trying to group by an aggregate output; hand-written queries mixing grouping levels that MySQL itself would reject outside derived tables.

Related errors

• only count(*) is supported: %v
• unsupported multiple columns in sum clause: %v
• unsupported non-column name in sum clause: %v
• unsupported aggregation function: %v
• unsupported non-column name or alias in group by clause: %v

AI-assisted analysis of vitessio/vitess@01a25a7d17 (2026-09-01). Data as JSON: /api/errors/86edec94d86c1a28. Report an issue: GitHub.