ORA-00979: Not a GROUP BY Expression Tips Oracle Error Tips by Donald Burleson

 

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.

The ORA-00979 error contains two components:

1. You attempted to execute a SELECT statement which contained a GROUP BY function such as MIN, MAX, SUM or COUNT.
2. 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:

1. Make the expression or column listed in the SELECT list also in the GROUP BY clause by completely rewriting the SELECT statement.
   
2. Completely remove the GROUP BY function from the SELECT statement, including MIN, MAX, SUM, and/or COUNT.
   
3. 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:

select
    emp_dept,
    emp_id,
    emp_name,
    sum(emp_comissions)
from
    my_emp
group by
    emp_dept,
    emp_id;

ORA-00979: not a GROUP BY expression