In my previous article I have given the different types of oracle errors with its resolution. We have also given the group by statement with real examples. There are so many times you will get error like not a single-group function and how to resolve this error step by step. The ORA-00937 error is most commonly coming oracle error when you try to use group by function. We will check the detailed steps about the resolving ora-00937 error.
What will you find in this article?
1.ORA-00937 : not a single-group function
2.How to resolve ORA-00937 error with example.
ORA-00937 : not a single-group function :
In this section we will see details about the ORA-00937 : not a single-group function error and will try to check why ora-00937 error is occurring.
The error ora-00937 error will come due to the group by command improperly used. you know that group by function is used to grouping the specified data in SQL.
Real example of group by :
If CEO wants count of employees who joined our company departmentwise then we need to use the group by function.
Select count(*) from Employees group by department;
The above statement is used to group the employees count by departments.
We require to remember that the group by function is used with aggregate functions to filter result by value. The cause of the above error is if you are using the group by statement with wrong syntax then above error will occur. If the set of data does not contain the group data then these kind of errors will occure.
Note : If you are using aggregate functions like AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE then you must need to use the group by with those functions. A SELECT list cannot include both a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, and an individual column expression, unless the individual column expression is included in a GROUP BY clause.
How to resolve the ORA-00937 error?
In this section we will see the resolution of ora-00937 error with examples.
To resolve the error, you can either remove the group function or column expression from the SELECT clause or you can add a GROUP BY clause that includes the column expressions.
Real example :
If you choose to add the GROUP BY clause, make sure to include the column expressions and follow the correct order. Take the example of the CEO who wants to view a list of employees who worked the most number of hours, organized by department. The correct syntax that includes the GROUP BY clause would be
FROM employees GROUP BY department;
SELECT department, MAX(hours) AS “Maximum Hours”
These are some most important examples and resolution of resolving the error ORA-00937 : not a single-group function. If you like this article or if you have any concerns with the same kindly comment in comments section.