// Oracle error reference

ORA-00979: not a GROUP BY expression

A column or expression in the select list, HAVING or ORDER BY isn't in the GROUP BY clause and isn't inside an aggregate function.

Error code
ORA-00979
Category
Syntax and SQL statements
Error message
not a GROUP BY expression

What it means

With GROUP BY, each output row stands for a group, so every selected expression must either be one of the grouping expressions or an aggregate. ORA-00979 means one isn't.

Newer releases name it: expression: must appear in the GROUP BY clause or be used in an aggregate function.

Common causes

  • Selecting a column that isn't in GROUP BY.
  • Grouping by an expression but selecting a slightly different one, such as TRUNC(order_date) versus order_date.
  • Ordering by a column that isn't grouped.

How to fix it

  1. Add the column to GROUP BY, or wrap it in an aggregate such as MAX().

    SELECT d.department_name, COUNT(*) AS staff
    FROM hr.employees e
    JOIN hr.departments d ON d.department_id = e.department_id
    GROUP BY d.department_name;
  2. Repeat the exact grouping expression in the select list.

    SELECT TRUNC(order_date) AS day, SUM(total)
    FROM orders
    GROUP BY TRUNC(order_date);

Related errors

More syntax and sql statements errors

The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.

// fewer errors, faster fixes

Work on Oracle in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for Oracle and the other databases you use.