ORA-00979: not a GROUP BY expression

Hi,

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

 

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

 

 

 

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

 

 1,173 views last month,  201 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience. I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource. I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks. I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients. If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com. -                                                                                                                                                                                                                                                 - Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

Check Also

ORA-01438: value larger than specified precision allowed for this column

Hi, I got ” ORA-01438: value larger than specified precision allowed for this column” error …

Leave a Reply