ORA-00937: not a single-group group function
ORA-00937: not a single-group group functionAn aggregate function (SUM, MAX, AVG...) is mixed with a plain column, and there is no GROUP BY.
Lab-verifiedReproduced and fixed on Oracle AI Database 26ai Free 23.26.3.0.0 · 2026-11-15
Without GROUP BY, an aggregate like SUM(sal) turns the whole table into one row. A plain column such as dept or name still has one value per row, so Oracle can't put both in the same result row.
The * in the error output points at the first plain column in the SELECT list. Decide what you actually want: one row per group (add GROUP BY), the row that has the aggregate value (subquery), or every row with the aggregate next to it (analytic function).
Causes and fixes
1Forgot GROUP BY for a per-group total
SELECT dept, SUM(sal) AS total FROM emp failed with * under dept. Listing the rows shows SALES has two salaries (500, 300) and IT one (400), so there is no single dept for one grand total.
Adding GROUP BY dept returned one row per department: IT 400, SALES 800.
Setup
CREATE TABLE emp (emp_id NUMBER PRIMARY KEY, name VARCHAR2(10), dept VARCHAR2(10), sal NUMBER);
INSERT INTO emp VALUES (1, 'KIM', 'SALES', 500);
INSERT INTO emp VALUES (2, 'LEE', 'SALES', 300);
INSERT INTO emp VALUES (3, 'PARK', 'IT', 400);
COMMIT;Table created.
1 row created.
1 row created.
1 row created.
Commit complete.SELECT dept, SUM(sal) AS total FROM emp;SELECT dept, SUM(sal) AS total FROM emp
*
ERROR at line 1:
ORA-00937: not a single-group group function
Help: https://docs.oracle.com/error-help/db/ora-00937/SELECT dept, sal FROM emp ORDER BY dept;DEPT SAL
---------- ----------
IT 400
SALES 500
SALES 300
3 rows selected.SELECT dept, SUM(sal) AS total FROM emp GROUP BY dept ORDER BY dept;DEPT TOTAL
---------- ----------
IT 400
SALES 800
2 rows selected.2Trying to get the name of the row with MAX()
SELECT name, MAX(sal) FROM emp looks like "who earns the most", but MAX() only returns the value. It failed on name. On its own, MAX(sal) returned 500.
Finding the row with that value in a subquery worked: WHERE sal = (SELECT MAX(sal) FROM emp) returned KIM 500.
Setup
CREATE TABLE emp (emp_id NUMBER PRIMARY KEY, name VARCHAR2(10), dept VARCHAR2(10), sal NUMBER);
INSERT INTO emp VALUES (1, 'KIM', 'SALES', 500);
INSERT INTO emp VALUES (2, 'LEE', 'SALES', 300);
INSERT INTO emp VALUES (3, 'PARK', 'IT', 400);
COMMIT;Table created.
1 row created.
1 row created.
1 row created.
Commit complete.SELECT name, MAX(sal) AS max_sal FROM emp;SELECT name, MAX(sal) AS max_sal FROM emp
*
ERROR at line 1:
ORA-00937: not a single-group group function
Help: https://docs.oracle.com/error-help/db/ora-00937/SELECT MAX(sal) AS max_sal FROM emp; MAX_SAL
----------
500
1 row selected.If several rows share the max value, this returns all of them.
SELECT name, sal FROM emp WHERE sal = (SELECT MAX(sal) FROM emp);NAME SAL
------------------------------ ----------
KIM 500
1 row selected.3Comparing each row with the average
SELECT name, sal, sal - AVG(sal) AS diff FROM emp failed because AVG(sal) collapses the table into one row while name and sal stay per row. On its own, AVG(sal) was 400.
The analytic form AVG(sal) OVER () keeps every row and puts the average next to it: KIM 100, LEE -100, PARK 0.
Setup
CREATE TABLE emp (emp_id NUMBER PRIMARY KEY, name VARCHAR2(10), dept VARCHAR2(10), sal NUMBER);
INSERT INTO emp VALUES (1, 'KIM', 'SALES', 500);
INSERT INTO emp VALUES (2, 'LEE', 'SALES', 300);
INSERT INTO emp VALUES (3, 'PARK', 'IT', 400);
COMMIT;Table created.
1 row created.
1 row created.
1 row created.
Commit complete.SELECT name, sal, sal - AVG(sal) AS diff FROM emp;SELECT name, sal, sal - AVG(sal) AS diff FROM emp
*
ERROR at line 1:
ORA-00937: not a single-group group function
Help: https://docs.oracle.com/error-help/db/ora-00937/SELECT AVG(sal) AS avg_sal FROM emp; AVG_SAL
----------
400
1 row selected.SELECT name, sal, sal - AVG(sal) OVER () AS diff FROM emp ORDER BY emp_id;NAME SAL DIFF
------------------------------ ---------- ----------
KIM 500 100
LEE 300 -100
PARK 400 0
3 rows selected.FAQ
What does ORA-00937 not a single-group group function mean?
The SELECT list has an aggregate function and a plain column, and there is no GROUP BY. The aggregate gives one row for the whole table, the plain column gives one value per row, and they can't be combined.
How do I fix ORA-00937?
For totals per group, add GROUP BY with the plain columns. For the row that has the max/min, use a subquery in WHERE. To show every row with a total or average, use OVER ().
How do I select the name together with MAX() in Oracle?
MAX() returns only the value. Use WHERE sal = (SELECT MAX(sal) FROM emp) to get the row(s) that have it.
What is the difference between ORA-00937 and ORA-00979?
ORA-00937 is when there is no GROUP BY at all. ORA-00979 is when GROUP BY exists but a selected column is missing from it.
How we verify
Each scenario starts from fresh database users. A script runs the setup, confirms the exact error code, applies the fix and runs the statement again. The output shown is what the database printed, saved by the script.
lab_a and lab_b are ordinary users created for the test; system is the DBA account. Oracle AI Database 26ai Free Release 23.26.3.0.0 - Develop, Learn, and Run for Free