ORA-00979: not a group by expression
Because the group by on empno and empname has basically no point (supposing they're unique in the table), a better way to do this would be:

select empno, empname
from emp.employee
where empmsal &gt; (select avg(empmsal) from emp.employee)


The thing is that (select avg(empmsal) from emp.employee) is computed only once - appearing in the where condition, and unless you have an index on empmsal it would perform a table scan, and then another for the main query. That's as best as it could get.



Another way is using window functions:

with cte as
( select empno, empname,
         avg(empmsal) over () as avg_empsal 
  from emp.employee
) 
select empno, empname
from cte
where empmsal &gt; avg_empmsal ;