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:

1SELECT department_id, SUM(salary)
2FROM employee;

If you ran this query, you would get an error:

1ORA-00937: not a single-group group function
200937. 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.

1SELECT department_id, SUM(salary)
2FROM employee
3GROUP BY department_id;

Now we can run the statement:

Solution 2: Remove the expression from the SELECT clause

Let's use the same query as solution 1 for our example:

1SELECT department_id, SUM(salary)
2FROM employee;

If you ran this query, you would get an error:

1ORA-00937: not a single-group group function
200937. 00000 - "not a single-group group function"

To resolve this error, we could remove the department_id from the SELECT clause.

1SELECT SUM(salary)
2FROM employee;

If we run the query, we get a single value for the SUM of all salaries.

Solution 3: Remove the aggregate function

Let's use the same query as solution 1 for our example:

1SELECT department_id, SUM(salary)
2FROM employee;

If you ran this query, you would get an error:

1ORA-00937: not a single-group group function
200937. 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.

1SELECT department_id, salary
2FROM employee;

Or, if we remove the column entirely:

1SELECT department_id
2FROM employee;

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.