Q:

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 > 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.

A:

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 > (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 > avg_empmsal ;

