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
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
deptname, but it is not clear what table they come from.