ORA-00979: Not a GROUP BY Expression Tips
Oracle Error Tips by Burleson Consulting
Use of the oerr utility reveals the following information on the ORA-00979 error:
ORA-00979 not a GROUP BY expression
Cause: The GROUP BY clause does not contain all the expressions in the SELECT clause. SELECT expressions that are not included in a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, must be listed in the GROUP BY clause.
Action: Include in the GROUP BY clause all SELECT expressions that are not group function arguments.
ORA-00979 contains two components:
You attempted to execute a SELECT statement which contained a GROUP BY function such as MIN, MAX, SUM or COUNT.
You attempted to execute an expression within the SELECT list which is not in the GROUP BY clause.
To correct ORA-00979, you can include all SELECT expressions in the GROUP BY clause which are not group function arguments.
Here are three ways to resolve ORA-00979:
Make the expression or column listed in the SELECT list also in the GROUP BY clause by completely rewriting the SELECT statement.
Completely remove the GROUP BY function from the SELECT statement, including MIN, MAX, SUM, and/or COUNT.
If there is an expression which is not in the GROUP BY clause, remove it completely from the SELECT list.
In the vast majority of vases, the ORA-00979 error is caused because a non-aggregated column is not included in the GROUP BY clause.
For example, in this case, a ORA-00979 error is thrown because the third column in the query is not included in the GROUP BY:
ORA-00979: not a GROUP BY expression