Database Error Messages

ORA-00979

expression: must appear in the GROUP BY clause or be used in an aggregate function
  • expression: The expression that was missing from the GROUP BY clause.

Cause

The specified expression was not part of either the GROUP BY clause, an aggregate function, or a constant but appeared in a part of the query that is processed after the GROUP BY clause, such as the SELECT clause, the ORDER BY clause, or the HAVING clause.

Action

Add the expression to the GROUP BY clause or add an aggregate function to specify which value of the expression to use for each group. Ensure that any columns in the SELECT list that are not part of an aggregate function are present in the GROUP BY clause.

Additional Information

The expression used is not part of the grouped result and can no longer be referenced.

The following clauses are processed after the GROUP BY clause:

  • HAVING clause
  • ORDER BY clause
  • SELECT clause

If you have an expression in any of these clauses that is not present in the GROUP BY clause, than that expression is not part of the grouped result produced by that clause. It can no longer be referenced in clauses that are processed after the GROUP BY clause.

If you want to produce an aggregated result for that expression for each group produced by the GROUP BY clause, then apply an aggregate function (for example, SUM(), AVG(), COUNT(), MIN(), and MAX()) over the expression instead.