ORA-00937: not a single-group group function

Sometimes You can get ”  ORA-00937: not a single-group group function ” error.

ORA-00937: not a single-group group function

Details of error are as follows.

ORA-00937: not a single-group group function

SQL> select sample_id,max(SAMPLE_TIME) from v$active_session_history;
select sample_id,max(SAMPLE_TIME) from v$active_session_history
*
ERROR at line 1:
ORA-00937: not a single-group group function

ORA-00937

This  ORA-00937 error is related with your SELECT clause.   Make sure that COUNT() and SUM() have to be grouped by all members in the SELECT clause, otherwise you will get this error.

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.

not a single-group group function

In other words, you tried to execute a SELECT statement that requires a GROUP BY clause without including the GROUP BY clause. If you are using an aggregate function in your select query (e.g. AVG, COUNT, MAX, MIN…), you must have a GROUP BY clause.

SQL> select sample_id,max(SAMPLE_TIME) from v$active_session_history;
select sample_id,max(SAMPLE_TIME) from v$active_session_history
*
ERROR at line 1:
ORA-00937: not a single-group group function

To solve this error, you should add group by clause with the missing column as follows.

SQL> select sample_id,max(SAMPLE_TIME) from v$active_session_history group by sample_id;

Or you can remove the sample_id column from SELECT clause, and it works fine.

SQL> select max(SAMPLE_TIME) from v$active_session_history;

MAX(SAMPLE_TIME)
---------------------------------------------------------------------------
14-MAY-20 04.57.59.450 PM

SQL>

If you got Error : ORA-00937: not a single-group group function occurs on SQL execution in Oracle 12.2.0.1, then apply the following patch.

Unpublished Bug 25435038 –  ORA-00600:[KKQCSCPOPNWITHMAP: 0] GBP AGG ON OBY WITH 2 TABLE JOIN-EXPLAIN PLAN

Install patch 25435038

Do you want to learn Oracle Database for Beginners, then Click and read the following articles.

Oracle Database Tutorials for Beginners ( Junior Oracle DBA )