ORA-00979: Not a GROUP BY Expression Tips

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

Source: http://www.dba-oracle.com/t_ora_00979_not_a_group_by_expression.htm

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: