Getting ORA-00979: not a GROUP BY expression error in oracle

Question

I am having "ORA-00979: not a GROUP BY expression" error in a query using group by and having. Can anybody tell me where the problem is? This is the query.

select DEPTNUM, INSTNAME, DEPTNAME from department
NATURAL join academic
group by DEPTNUM
having count(deptnum) > 10

you are grouping just one column, while selecting other columns that are not in an aggregation function — Lamak, Sep 25 '17 at 18:48
Unlike mySQL, Oracle requires you to have any field in the select which is not aggregated in the group by. — xQbert, Sep 25 '17 at 18:50

score 0 · Answer 1 · answered Sep 25 '17 at 18:49

0

Either add INSTNAME, DEPTNAME in the GROUP BY, or use aggregate function (such as MIN, MAX) for INSTNAME, DEPTNAME

answered Sep 25 '17 at 18:49

Andrey Belykh

2,578
4
32
46

or remove the unaggregated columns from the select. – xQbert Sep 25 '17 at 18:51

score 0 · Answer 2 · answered Sep 25 '17 at 18:52

If you need and ggreted result on a column you shoudl not have this column in group by
and your error is related to the fact you have not column in group by clause taht are not aggregated in select

so try

select INSTNAME, DEPTNAME from department
NATURAL join academic
group by INSTNAME, DEPTNAME
having count(deptnum) > 10

score 0 · Accepted Answer · answered Sep 25 '17 at 18:54

Eschew natural join. It is pretty much an abomination, because it uses columns that have the same name rather than declared foreign key relationships.

Your real problem is the group by and select. I would suggest:

select DEPTNUM, INSTNAME, DEPTNAME
from department d join
     academic a
     using (deptnum)
group by DEPTNUM, INSTNAME, DEPTNAME
having count(deptnum) > 10;

I would also qualify instname and deptname, but it is not clear what table they come from.

Getting ORA-00979: not a GROUP BY expression error in oracle

3 Answers3