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.
Ora-00900: Invalid SQL Statement On EXECUTE IMMEDIATE Call PL/SQL object (Doc ID 2503264.1) Last updated on DECEMBER 19, 2019. Applies to: PL/SQL - Version 11.2.0.4 and later. ORA 00900: invalid SQL statement or ORA-06553. The database is Oracle 12c and only Toad for Oracle 12 or later supports Oracle 12c. Error: 'ORA-00900: invalid SQL statement' after executing procedure Oracle 10g Express Edition, Windows 7.
ORA-00979: not a group by expression
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:
Result:
Oracle Ora 00904 Invalid Identifier
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:
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 column 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:
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:
Result:
| FIRST_NAME | LAST_NAME | COUNT(*) |
| John | Smith | 2 |
| Mark | Anderson | 1 |
| Michael | Condor | 1 |
| Brendan | Jefferson | 1 |
| Peter | Stark | 1 |
| Sally | Lincoln | 1 |
| Michelle | Brumby | 1 |
| Amy | Ford | 1 |
| Rose | Minson | 1 |
| Tina | Mitchell | 1 |
Or, using the second example:
Result:
| FIRST_NAME | LAST_NAME | ADDRESS_STATE | COUNT(*) |
| John | Smith | California | 1 |
| Mark | Anderson | Texas | 1 |
| Michael | Condor | Florida | 1 |
| Brendan | Jefferson | Florida | 1 |
| Peter | Stark | Illinois | 1 |
| Sally | Lincoln | California | 1 |
| Michelle | Brumby | Texas | 1 |
| Amy | Ford | Texas | 1 |
| Rose | Minson | California | 1 |
| Tina | Mitchell | Illinois | 1 |
| John | Smith | Texas | 1 |
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.
Ora 00900 Error
So, if you have a query like this:
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:
| FULL_NAME | COUNT(*) |
| John Smith | 2 |
| Mark Anderson | 1 |
| Michael Condor | 1 |
| Brendan Jefferson | 1 |
| Peter Stark | 1 |
| Sally Lincoln | 1 |
| Michelle Brumby | 1 |
| Amy Ford | 1 |
| Rose Minson | 1 |
| Tina Mitchell | 1 |
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!
Last updated on DECEMBER 19, 2019
Applies to:
PL/SQL - Version 11.2.0.4 and laterGeneric (Platform Independent)
Symptoms
EXECUTE IMMEDIATE failed when calling PL/SQL object:
Changes
None
Cause
To view full details, sign in with your My Oracle Support account. |
Don't have a My Oracle Support account? Click to get started! |
Ora 00900 Stored Procedure
In this DocumentOra 20900
| Symptoms |
| Changes |
| Cause |
| Solution |
| References |