Not a group by expression oracle ошибка

Have you tried to run a query and got the “ORA-00979: not a group by expression” error? Learn what it is and how to resolve the error in this article.

The ORA-00979 error happens when you have at least one column in your SELECT clause that is not in your GROUP BY expression when you are using an aggregate function.

Common aggregate functions include SUM, AVG, MIN, MAX, and COUNT. Any column or expression in your SELECT clause must also be listed in the GROUP BY clause.

Here’s an example of a query that will generate the error:

SELECT first_name, last_name, COUNT(*)
FROM student
GROUP BY first_name;

Result:

ORA-00979: not a GROUP BY expression

Why Do I Get The ORA-00979 Error?

This error happens because you’re using an aggregate function, and there is at least one column in the SELECT clause that is not in the GROUP BY clause.

Using the example query above:

SELECT first_name, last_name, COUNT(*)
FROM student
GROUP BY first_name;

Because I use an aggregate function (COUNT), I need to define all of the columns in a GROUP BY clause that are in the SELECT clause.

In this example, I have specified the first_name and last_name columns in the SELECT clause, but the last_name column is not in the GROUP BY clause.

Even if I have some fields in the GROUP BY clause, if I don’t specify all of the fields from the SELECT clause, I’ll still get an error.

For example:

SELECT first_name, last_name, address_state, COUNT(*)
FROM student
GROUP BY first_name, last_name

Result:

ORA-00979: not a GROUP BY expression

As you can see, this will still give me an error.

Why does Oracle give an error?

Because, if you don’t have a GROUP BY but you want to SELECT the column, Oracle doesn’t know what value to show when using this aggregate function. Should it show the first value? The last value? A random value?

How To Resolve the ORA-00979 Error

To resolve the ORA-00979: not a group by expression error, simply ensure that all of the GROUP BY columns match the SELECT clause.

You can do this by adding columns to the GROUP BY.

So, using the example above:

SELECT first_name, last_name, COUNT(*)
FROM student
GROUP BY first_name, last_name;

Result:

Or, using the second example:

SELECT first_name, last_name, address_state, COUNT(*)
FROM student
GROUP BY address_state, first_name, last_name

Result:

The columns don’t need to be in the same order to correct the error. They just need to be present.

One thing to remember with a GROUP BY clause, is if you give your columns aliases, you need to specify the original column or expression.

So, if you have a query like this:

SELECT first_name + ' ' + last_name AS full_name, COUNT(*)
FROM student
GROUP BY full_name;

Result:

ORA-00904: "FULL_NAME": invalid identifier

You get an error because you can’t refer to a column alias within the GROUP BY clause. You’ll need to use a query like this:

SELECT first_name + ' ' + last_name AS full_name, COUNT(*)
FROM student
GROUP BY first_name + ' ' last_name;

Conclusion

So, in conclusion, the ORA-00979: not a group by expression error happens because the columns in the SELECT clause don’t match the columns in the GROUP BY clause. To resolve the error, make sure the columns match.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

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.