ORA-00979: not a GROUP BY expression

Sometimes You can get ” ORA-00979: not a GROUP BY expression ” error.

ORA-00979: not a GROUP BY expression

Details of error are as follows.

ORA-00979 not a GROUP BY expression

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-00979 not a GROUP BY expression

not a GROUP BY expression

This  ORA-00979 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.

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-00979 not a GROUP BY expression

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-00979 not a GROUP BY expression 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 more details about Synonym, then read the following post.

Create and Drop Synonym Tips in Oracle with Examples