ORA-00937
ORA-00937: не отдельная группа групповой функции
Причина:
SELECT список не может быть включать вдвоем как и групповую функцию как (AVG, COUNT, MAX, MIN, SUM, STDDEV, или VARIANCE) и индивидуальное выражение колонки включенное в предложение GROUP BY.
Действие:
Проще всего удалите групповую функцию или индивидуальное выражение колонки из SELECT списка, или добавить GROUP BY предложение, которое бы включало все индивидуальные выражения колонки в списке.
Learn the cause and how to resolve the ORA-00937 error message in Oracle.
Description
When you encounter an ORA-00937 error, the following error message will appear:
- ORA-00937: not a single-group group function
Resolution
The option(s) to resolve this Oracle error are:
Option #1
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 Function, MAX Function, SUM Function, COUNT Function) from the SELECT statement.
Option #3
Remove the expression from the SELECT list that was not in the GROUP BY clause.
For example, if you had tried to execute the following SELECT statement:
SELECT department, MIN(salary) AS "Lowest salary" FROM employees;
You would receive the following error message:
You could correct this by including department in the GROUP BY clause as follows:
SELECT department, MIN(salary) AS "Lowest salary" FROM employees GROUP BY department;
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.
Так в чём же причина ошибки (или заблуждение автора вопроса).
Оператор суммирования + выполнется для значений колонок или выражений полученных из одной записи (по горизонтали). Групповая функция SUM() выполнется для значений колонок или выражений всех записей группы (по вертикали), а группа без клаузы GROUP BY это всегда все записи полученные после условия фильтрации в WHERE.
То есть, на таких тестовых данных, после простейшего запроса:
create table t (id, fname, lname) as
select 1, 'aaa', 'bbb' from dual union all
select 2, 'cc', 'dd' from dual
/
select sum (length (fname) + length (lname))
from t
автор вопроса ожидал бы получить две записи со значениями 6 и 4, однако результат:
SUM
----------
10
Но интересен же результат для каждой записи со своим ID (или другими столбцами):
select id, sum (length (fname) + length (lname)) sumlen
from t
ORA-00937: not a single-group group function
В этом случае БД должна вывести все ID, то есть, так получится несколько групп записей.
Об этом и говорится в сообщении об ошибке — групповая функция может посчитать значения только одной группы, но никак не нескольких.
Решение очевидно — групповая функция SUM в запросе явно лишняя:
select *
from t
order by (length (fname) + length (lname));
ID FNA LNA
---------- --- ---
2 cc dd
1 aaa bbb
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.