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 s

   uch 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