Have you received an ORA-00937: not a single-group group function error? Learn what it is and how to fix it in this article.
The ORA-00937 error occurs when a query has an aggregate function (e.g. COUNT, MIN, MAX, SUM, or AVG) as well as other fields or statements, but there is no GROUP BY clause.
When you use one of these aggregate functions by itself, you can run a query without a GROUP BY clause. But, if you use it with another field or column, you need to use a GROUP BY clause to avoid the ORA-00937 error.
Let’s see the solutions to this which include some examples.
ORA-00937 Solution
There are a few ways you can resolve the ORA-00937: not a single-group group function error:
- Add the expressions that are in the SELECT clause into the GROUP BY clause (and add a GROUP BY clause if one doesn’t exist).
- Remove any other columns besides the aggregate function from your SELECT clause.
- Remove the aggregate function from the SELECT clause.
Solution 1: Add the expressions into the GROUP BY clause
Let’s say you had a query that looked like this:
SELECT department_id, SUM(salary)
FROM employee;
If you ran this query, you would get an error:
ORA-00937: not a single-group group function 00937. 00000 - "not a single-group group function"
This is because there is no GROUP BY clause, and you’re using an aggregate function (the SUM function) along with another field (department_id).
To correct this, add a GROUP BY clause, and add all expressions/columns to the GROUP BY clause that are in the SELECT clause and not an aggregate function. In this case, it’s the department_id column.
SELECT department_id, SUM(salary)
FROM employee
GROUP BY department_id;
Now we can run the statement:
DEPARTMENT_ID | SUM(SALARY) |
1 | 305000 |
212000 | |
6 | 2599500 |
2 | 2142000 |
5 | 2491000 |
4 | 1367300 |
8 | 1560000 |
3 | 2233000 |
7 | 1587000 |
Solution 2: Remove the expression from the SELECT clause
Let’s use the same query as solution 1 for our example:
SELECT department_id, SUM(salary)
FROM employee;
If you ran this query, you would get an error:
ORA-00937: not a single-group group function 00937. 00000 - "not a single-group group function"
To resolve this error, we could remove the department_id from the SELECT clause.
SELECT SUM(salary)
FROM employee;
If we run the query, we get a single value for the SUM of all salaries.
SUM(SALARY) |
14496800 |
Solution 3: Remove the aggregate function
Let’s use the same query as solution 1 for our example:
SELECT department_id, SUM(salary)
FROM employee;
If you ran this query, you would get an error:
ORA-00937: not a single-group group function 00937. 00000 - "not a single-group group function"
Another way to resolve this error is to remove the aggregate function. We could either leave the salary column there or remove it.
Here’s the query with the column remaining.
SELECT department_id, salary
FROM employee;
DEPARTMENT_ID | SALARY |
8 | 48000 |
3 | 79000 |
7 | 47000 |
3 | 51000 |
1 | 117000 |
7 | 21000 |
6 | 76500 |
7 | 34000 |
7 | 92000 |
8 | 32000 |
… | … |
Or, if we remove the column entirely:
SELECT department_id
FROM employee;
DEPARTMENT_ID |
8 |
3 |
7 |
3 |
1 |
7 |
6 |
7 |
7 |
8 |
… |
As you can see, there are several ways to resolve the ORA-00937: not a single-group group function error. It depends on what data you want to be returned from your query. It’s usually a simple fix, and I most often see it when I forget to add a GROUP BY to my query.
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!
- Table of contents
- Error code 937, SQL state 42000: ORA-00937: not a single-group group function
- SQL Error [937] [42000]: ORA-00937: not a single-group group function
- ORA-00937: not a single-group group function Solution
- ORA-00937: not a single-group group function
- Oracle / PLSQL: ORA-00937 Error Message
Error code 937, SQL state 42000: ORA-00937: not a single-group group function
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.
SELECT DP.CODE_VALEUR CODE, MAX(VA.CODE_TYPE_VALEUR) CODE_TYPE_VALEUR, MAX(VA.NOM_VALEUR) STOCK_NAME, (SUM(COURS_ACQ_VALEUR) / SUM(QUANTITE_VALEUR)) CMP, MAX(DP.CODE_COMPTE) CODE_COMPTE, SUM(DP.QUANTITE_VALEUR) QTEVALEUR, round(SUM(DP.VALORISATION_BOURSIERE), 3) VALORISATION_BOURSIERE, round((SUM(DP.VALORISATION_BOURSIERE) / SUM(DP.QUANTITE_VALEUR)), 3) COURS FROM DETAILPORTEFEUILLE DP, VALEUR VA WHERE DP.CODE_COMPTE IN (SELECT P.CODE_COMPTE_RATTACHE FROM PROCURATION P WHERE P.IDWEB_MASTER = 8 AND NVL(P.CAN_SEE_PORTEFEUILLE, 0) != 0) AND VA.CODE_VALEUR = DP.CODE_VALEUR AND DP.QUANTITE_VALEUR > 0 AND DP.CODE_VALEUR = 'TN0007250012'
GROUP BY DP.CODE_VALEUR
GROUP BY DP.CODE_VALEUR
SQL Error [937] [42000]: ORA-00937: not a single-group group function
Teams. Q&A for work. Connect and share knowledge within a single location that is structured and easy to search. Learn more
WITH BOXCOUNT AS ( SELECT PRODUCT_CODE , ROUND(SUM(TOTAL_QUANTITY/PACKING_STYLE)) AS FULLBOX , CASE WHEN SUM((TOTAL_QUANTITY - ROUND(SUM(TOTAL_QUANTITY/PACKING_STYLE))) * PACKING_STYLE) = 0 THEN 0 WHEN SUM((TOTAL_QUANTITY - ROUND(SUM(TOTAL_QUANTITY/PACKING_STYLE))) * PACKING_STYLE) > 0 THEN 1 END AS SPAREBOX FROM LOG0104M GROUP BY PRODUCT_CODE) SELECT L04.PRODUCT_CODE , L04.PRODUCT_NAME , L04.TOTAL_QUANTITY , L04.PACKING_STYLE , BC.FULLBOX , BC.SPAREBOX , SUM(BC.FULLBOX + BC.SPAREBOX) AS TOTALBOX FROM LOG0104M L04 JOIN BOXCOUNT BC ON BC.PRODUCT_CODE = L04.PRODUCT_CODE GROUP BY L04.PRODUCT_CODE , L04.PRODUCT_NAME , L04.TOTAL_QUANTITY , L04.PACKING_STYLE , BC.FULLBOX , BC.SPAREBOX
SQL Error [937] [42000]: ORA-00937: not a single-group group function
CASE WHEN SUM((TOTAL_QUANTITY - ROUND(SUM(TOTAL_QUANTITY/PACKING_STYLE))) * PACKING_STYLE) = 0 THEN 0 WHEN SUM((TOTAL_QUANTITY - ROUND(SUM(TOTAL_QUANTITY/PACKING_STYLE))) * PACKING_STYLE) > 0 THEN 1 END AS SPAREBOX
sum(CASE WHEN ((TOTAL_QUANTITY - ROUND((TOTAL_QUANTITY/PACKING_STYLE))) * PACKING_STYLE) = 0 THEN 0 WHEN ((TOTAL_QUANTITY - ROUND((TOTAL_QUANTITY/PACKING_STYLE))) * PACKING_STYLE) > 0 THEN 1 END) AS SPAREBOX
ORA-00937: not a single-group group function Solution
Solution 1: Add the expressions into the GROUP BY clause. ORA-00937: not a single-group group function 00937. 00000 — «not a single-group group …
SELECT department_id, SUM(salary) FROM employee;
ORA-00937: not a single-group group function 00937. 00000 - "not a single-group group function"
SELECT department_id, SUM(salary) FROM employee GROUP BY department_id;
SELECT department_id, SUM(salary) FROM employee;
ORA-00937: not a single-group group function 00937. 00000 - "not a single-group group function"
SELECT SUM(salary) FROM employee;
SELECT department_id, SUM(salary) FROM employee;
ORA-00937: not a single-group group function 00937. 00000 - "not a single-group group function"
SELECT department_id, salary FROM employee;
SELECT department_id FROM employee;
ORA-00937: not a single-group group function
I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance …
ORA-00937: not a single-group group function SQL> select sample_id,max(SAMPLE_TIME) from v$active_session_history; select sample_id,max(SAMPLE_TIME) from v$active_session_history * ERROR at line 1: ORA-00937: not a single-group group function
SQL> select sample_id,max(SAMPLE_TIME) from v$active_session_history; select sample_id,max(SAMPLE_TIME) from v$active_session_history * ERROR at line 1: ORA-00937: not a single-group group function
SQL> select sample_id,max(SAMPLE_TIME) from v$active_session_history group by sample_id;
SQL> select max(SAMPLE_TIME) from v$active_session_history; MAX(SAMPLE_TIME) --------------------------------------------------------------------------- 14-MAY-20 04.57.59.450 PM SQL>
ORA-00937: not a single-group group function
Cause: 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 …
select s.student_id, s.first_name, s.last_name, count(o.order_id) No from students s, orders o where s.student_id = o.student_id order by s.student_id;
select s.student_id, s.first_name, s.last_name, count(o.order_id) No from students s, orders o where s.student_id = o.student_id group by s.student_id, s.first_name, s.last_name, o.order_id order by s.student_id;
Oracle / PLSQL: ORA-00937 Error Message
Rewrite the SELECT statement so that the column or expression listed in the SELECT list is also found in the GROUP BY clause. Option #2. Remove the GROUP BY function (ie: MIN …
SELECT department, MIN(salary) AS "Lowest salary" FROM employees;
SELECT department, MIN(salary) AS "Lowest salary" FROM employees GROUP BY department;
Next Lesson PHP Tutorial
May 14, 2020
Sometimes You can get ” ORA-00937: not a single-group group function ” error.
ORA-00937: not a single-group group function
Details of error are as follows.
ORA-00937: not a single-group group function SQL> select sample_id,max(SAMPLE_TIME) from v$active_session_history; select sample_id,max(SAMPLE_TIME) from v$active_session_history * ERROR at line 1: ORA-00937: not a single-group group function
ORA-00937
This ORA-00937 error is related with your SELECT clause. Make sure that COUNT() and SUM() have to be grouped by all members in the SELECT clause, otherwise you will get this error.
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.
not a single-group group function
In other words, you tried to execute a SELECT statement that requires a GROUP BY clause without including the GROUP BY clause. If you are using an aggregate function in your select query (e.g. AVG, COUNT, MAX, MIN…), you must have a GROUP BY clause.
SQL> select sample_id,max(SAMPLE_TIME) from v$active_session_history; select sample_id,max(SAMPLE_TIME) from v$active_session_history * ERROR at line 1: ORA-00937: not a single-group group function
To solve this error, you should add group by clause with the missing column as follows.
SQL> select sample_id,max(SAMPLE_TIME) from v$active_session_history group by sample_id;
Or you can remove the sample_id column from SELECT clause, and it works fine.
SQL> select max(SAMPLE_TIME) from v$active_session_history; MAX(SAMPLE_TIME) --------------------------------------------------------------------------- 14-MAY-20 04.57.59.450 PM SQL>
If you got Error : ORA-00937: not a single-group group function occurs on SQL execution in Oracle 12.2.0.1, then apply the following patch.
Unpublished Bug 25435038 – ORA-00600:[KKQCSCPOPNWITHMAP: 0] GBP AGG ON OBY WITH 2 TABLE JOIN-EXPLAIN PLAN
Install patch 25435038
Do you want to learn Oracle Database for Beginners, then Click and read the following articles.
Oracle Database Tutorials for Beginners ( Junior Oracle DBA )
2,058 views last month, 2 views today
About Mehmet Salih Deveci
I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA, Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].- -Oracle DBA, SQL Server DBA, APPS DBA, Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için [email protected] a mail atabilirsiniz.
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.
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.