ORA-00979: not a group by expression | TekStream Solutions

ORA-00979

ORA-00979 is related to the GROUP BY clause. When a user encounters this error, the following message will be displayed:

ORA-00979: not a GROUP BY expression

In comparison to other Oracle errors, ORA-00979 is usually straightforward and can easily be solved by one of three methods.

The Problem

ORA-00979 occurs when the GROUP BY clause does not contain all the expressions in the SELECT clause. Any SELECT expression that is not included in the GROUP function must be listed in the GROUP BY clause. These are AVG, COUNT, MAX, MIN, SUM, STDDEV, and VARIANCE. You may have also tried to execute a SELECT statement that contains a GROUP BY clause.

The Solution

To correct this error, include all of the SELECT expressions in the GROUP BY clause. Make sure the expressions are not group function arguments. There are namely three methods for resolving this error.

• Rewrite the SELECT statement so that the expression or column listed in the SELECT list is also in the GROUP BY clause.
• You may choose to remove the GROUP BY function from the SELECT statement altogether.
• Remove any expressions that do not belong in the GROUP BY clause from the SELECT list.

The first option in fixing the error would apply to the following example in which a user tries to execute a SELECT statement:

SELECT department, class, MAX(number) AS “Highest number of students”

FROM students

GROUP BY department;

In this example, the error may be corrected by including class in the GROUP BY clause. Class is included in both the SELECT and GROUP BY statements.

SELECT department, class, MAX(number) AS “Highest number of students”

FROM students

GROUP BY department, class;

Looking Forward

To avoid seeing ORA-00979, make sure that expressions in the SELECT list are also included in the GROUP BY clause. If the expression is also in the GROUP BY clause, you should not see the error. If you continue to see the error and have trouble resolving the issue, contact your database administrator. You may also consider contacting a licensed Oracle consultant. Before using their services, always make sure that they have proper credentials the level of experience needed to handle your Oracle needs.