ORA-00979: not a group by expression I'm getting the error: 'ORA-00979: not a group by expression' when I try to run this query.

select empno, empname
from emp.employee
group by empno, empname
having empmsal &gt; avg(empmsal);


I'm attempting to find employees with a salary above the average.
Can you see anything wrong with the query?

Sorry if this is obvious. I'm new to sql.
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 ;