 |
|
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:
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
