ORA-00979 is a common error which is associated with invalid usage of the group by clause. Here is the oerr output for this error
Causes of ORA-00979 not a group by expression
This error happens when you are using a mixture of individual columns and group functions in the same select statement while missing the column selected in the group by clause.
When this error happens, the oracle server reports “not a GROUP BY expression” error and asterisk (*) going to the offending column
CREATE TABLE "EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
);
insert into emp values( 1000, 'Blake', 'MANAGER', 7839, to_date('1-5-2007','dd-mm-yyyy'), 2850, null, 10 );
insert into emp values( 1001, 'Clark', 'MANAGER', 7839, to_date('9-6-2008','dd-mm-yyyy'), 2450, null, 10 );
insert into emp values( 1002, 'Scott', 'ANALYST', 7566, to_date('9-6-2012','dd-mm-yyyy'), 3000, null, 20 );
insert into emp values( 1003, 'TPM', 'ANALYST', 7566, to_date('9-6-2017','dd-mm-yyyy'), 3000, null, 20 );
insert into emp values( 1004, 'T1OM', 'ANALYST', 7567, to_date('9-7-2017','dd-mm-yyyy'), 4000, null, 20 );
insert into emp values( 1005, 'TOM', 'ANALYST', 7567, to_date('9-7-2017','dd-mm-yyyy'), 4000, null, 30 );
commit;
select deptno,job, avg(sal) from emp group by deptno;
select deptno,job, avg(sal) from emp group by deptno;
*
Error at line 1
ORA-00979: not a GROUP BY expression
Checklist to run to resolve ORA-00979 not a group by expression
(1) Whenever we are using the Individual item and Group function in the same select statement, then we must include all individual items in the group clause. So Right query for the above error will be
select deptno,job , avg(salary) from emp group by deptno,job;
(2) This can also happen when you are using more than two columns in the select statement with a group statement and you have used the group by clause but you forget to attach all the selected columns in group by
Select col1 ,col2,col3, sum(col5) from test group by col1,col2;
Select col1 ,col2,col3 ,sum(col5) from test group by col1,col2;
*
Error at line 1
ORA-00979: not a GROUP BY expression
The correct query will be
Select col1 ,col2,col3, sum(col5) from test group by col1,col2,col3;
(3) Sometimes, this could happen because of the Oracle bug also. We saw these errors in the recent upgraded 19c database also. The queries were working before Upgrade.
Oracle asked to apply Patch 31504723 or below workaround
alter session set "_simple_view_merging" = false; or alter session set "_fix_control"='8528517:0';
I hope you like these simple tips on ORA-00979: not a group by expression
Related Articles
not a single-group group function : Check out a method to resolve ORA-00937: not a single-group group function. This error happens when you are using group functions
Analytic functions in oracle : Oracle Analytic functions compute an aggregate value based on group of rows by using over partition by oracle clause , they differ from aggregate functions
rank in oracle : RANK, DENSE_RANK and ROW_NUMBER are oracle analytical function which are used to rank rows in the group of rows called window
Lead function in oracle : Check out LAG function in Oracle & Lead function in Oracle, how to use them in analytic queries and how it works in Oracle sql
Top-N Queries in Oracle : Check out this page on exploring the various ways to achieve Top-N Queries in Oracle like oracle top 100, selecting top 10 rows in oracle
Auto Increment in Oracle : Check out how to use Auto Increment in Oracle before 12c version and with 12 and above version with detailed examples
group by oracle : GROUP BY oracle is used to group rows into a set of summary rows by values of single or multiple columns. Group by oracle order by
https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqlj32654.html