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 Cause

The cause of the ORA-00937 error is that a SELECT statement is trying to be executed, and the SELECT statement 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.ORA-00937

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.

Let’s see the solutions to this which includes some examples.

Want an Oracle SQL function cheat sheet for easy reference? Click here to get a PDF file that contains all of Oracle functions for you to reference or print out.

 

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.

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!

Want an Oracle SQL function cheat sheet for easy reference? Click here to get a PDF file that contains all of Oracle functions for you to reference or print out.

Improve Your Oracle SQL With My 10-Day Email Course

x