ORA-00979: not a group by expression

Question

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.

Answer 1

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 ;

Answer 2

Assume the following employee table

+-------+---------+--------+-------+-------+
| empid | empname |    sal | depid | jobid |
+-------+---------+--------+-------+-------+
|    11 | smith   |   2500 |     2 |     6 |
|    32 | doe     |   3500 |     3 |     6 |
|    17 | clark   |   3000 |     2 |     4 |
|    24 | wayne   |   4000 |     3 |     6 |
|    37 | lane    |   3000 |     2 |     4 |
|    21 | johnson |   4000 |     3 |     4 |
+-------+---------+--------+-------+-------+

Now a query

select ...
from employee
group by depid, jobid

constructs three groups of data represented by the three rows in the following graphic

+-------+-------++--------------------------++---------------------------------+
| group  by     ||    remaining             ||          aggregates             |
+-------+-------++-------+---------+--------++-------+---------+---------+-----+
| depid | jobid || empid | empname |    sal || count | sum(sal)| avg(sal)| ... | 
+-------+-------++-------+---------+--------++-------+---------+---------+-----+
|     2 |     6 ||       |         |        ||     1 |    2500 |    2500 | ... | 
|       |       ||    11 | smith   |   2500 ||       |         |         |     |
+-------+-------++-------+---------+--------++-------+---------+---------+-----+
|     3 |     6 ||       |         |        ||     2 |    7500 |    3750 | ... | 
|       |       ||    32 | doe     |   3500 ||       |         |         |     |
|       |       ||    24 | wayne   |   4000 ||       |         |         |     |
+-------+-------++-------+---------+--------++-------+---------+---------+-----+
|     2 |     4 ||       |         |        ||     2 |    6000 |    3000 | ... | 
|       |       ||    17 | clark   |   3000 ||       |         |         |     |
|       |       ||    37 | johnson |   3000 ||       |         |         |     |
+-------+-------++-------+---------+--------++-------+---------+---------+-----+

Each row in these groups contains one value for depid and jobid (the columns we grouped by) and one or more values for the remaining employee table columns. For every group arbitrary aggregates (sum,count,max,avg,...) can be calculated and returned by the query if they are listed in the select clause of the query.

The having clause filters out some of these groups. So

select ...
from employee
group by depid, jobid
having avg(sal)>3700

discards the two groups with depid=2 and jobid=6 and depid=2 and jobid=4. So only the group depid=3 and jobid=6 remains.

A filter condition like

select ...
from employee
group by depid, jobid
having sal>3700

that uses one or more of the remaining columns but not using aggregate functions does not make sense at this group level. It is not clear how this filter condition should be applied to group depid=3 and jobid=6.

That is why the database throws an error message whe you execute your statement. This will happen even if you grouip by an index column and therefor all of your groups contain only one row of the source table.