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
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)versusorder_date. - Ordering by a column that isn't grouped.
How to fix it
-
Add the column to
GROUP BY, or wrap it in an aggregate such asMAX().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;
-
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
ORA-00937not a single-group group function8120SQL Server: Column not in GROUP BY or aggregate1055MySQL: Expression isn't in GROUP BY42803PostgreSQL: column must appear in the GROUP BY clause
More syntax and sql statements errors
ORA-00933SQL command not properly endedORA-00936missing expressionORA-00907missing right parenthesisORA-00911invalid characterORA-00917missing commaORA-00918column ambiguously definedORA-00923FROM keyword not found where expectedORA-01427single-row subquery returns more than one row
The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.
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.