Kamal Ashraf Gill Kamal Ashraf Gill - 1 month ago 19
SQL Question

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

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

Answer Source

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.