While writing SQL queries there are times when we face many errors. The possibility of facing errors depends on the way you are writing queries, either it can be a typo mistake or wrong syntax. Although error messages are helpful in troubleshooting, they may not always be helpful. “Not A Group By Expression” Error is an example of this. In this tutorial, we are going to find out why we are facing this error and find a solution to fix it.
Demonstrating the Error
Oracle databases show the error ORA-00979: “not a group by expression” when a SELECT statement contains a column that is not specified in GROUP BY while using an aggregate function. This message can be confusing for new users in a database query. As we can recall GROUP BY, there is a general rule that “every column which is specified in the SELECT statement should be either called in aggregate function or the GROUP BY clause”.
Example
Below is the table named People which we are going to use for the rest of this tutorial.
Here’s an example of a query that will return the error:
SELECT First_Name, Country, Age, COUNT(*)
FROM People
GROUP BY First_Name, Country;Code language: SQL (Structured Query Language) (sql)
In the above output, we can see that we got the error.
Why Does Oracle Give This Error?
We got this error because we are using the aggregate function and there is at least one column in the SELECT statement that is not included in the GROUP BY clause. As we use an aggregate function i.e., COUNT, we need to specify all the columns in the GROUP BY clause which are present in the SELECT statement.
In the previous example, we have specified the First_name, Country and Age in the SELECT statement, but the Age is not specified in the GROUP BY clause.
Also Read: Common GROUP BY Error in SQL
How to Fix this Error?
If we want to fix the ORA-00979: ‘not a group by expression error’, we can make sure that all the column that are in the GROUP BY clause matches with the columns in the SELECT statement. This can be done by adding columns to the GROUP BY clause.
Example
We can do the following:
SELECT First_Name, Country, Age, COUNT(*)
FROM People
GROUP BY First_Name, Country, Age;Code language: SQL (Structured Query Language) (sql)
or we can use the below query:
SELECT First_Name, Country, COUNT(*)
FROM People
GROUP BY First_Name, Country;Code language: SQL (Structured Query Language) (sql)
As we can observe from the above examples we have not got the error. Also, one can wonder if the column is not in the same order, will we get the error? The answer is simply no! They are required to be present doesn’t matter in which order.
If we are giving aliases to our columns, we need to specify the original column.
SELECT First_Name || ' ' || Father_Name AS Family, COUNT(*)
FROM People
GROUP BY Family;Code language: SQL (Structured Query Language) (sql)
In the above example, we can see that we have got an error, it’s because we cannot refer to column alias within the GROUP BY clause. We are required to write the query as:
SELECT First_Name || ' ' || Father_Name AS Family, COUNT(*)
FROM People
GROUP BY First_Name || ' ' || Father_Name;Code language: SQL (Structured Query Language) (sql)
Now, as we can see from the above, the query is successfully executed and we have faced no error.
Conclusion
In this tutorial, we have learned how to solve ORA-00979: ‘not a group by expression’ error. We examined the scenarios in which we can face this error and understood the importance of ensuring that the columns in the SELECT statement properly match with the columns in the GROUP BY clause. It’s good practice if we avoid these errors while writing our SQL query which can make our code in good shape. We hope you enjoyed this article.
Reference
https://stackoverflow.com/questions/1520608/ora-00979-not-a-group-by-expression