ORA-00937: not a single-group group function Solution

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.

ORA-00937: not a single-group group function

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:

1. 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).
2. Remove any other columns besides the aggregate function from your SELECT clause.
3. 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.