Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
> SELECT AVG(empsal) FROM emp.employee –  Mihai Apr 29 at 5:49
    
You'll also find lots of similar questions on Stack Overflow. –  Mark Hurd Apr 29 at 14:34

2 Answers 2

up vote 3 down vote accepted

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 ;
share|improve this answer
    
Not sure what you mean regarding only being computed once but I think this query is exactly what I was looking for (I didn't think of doing a subquery). Thank you. –  Patrick Apr 29 at 6:42
    
Each time you have some kind of computation in a where condition, it can be computed either once (at the beginning), or for each row. For instance the average does not depend on any row, and is the same per whole table, so the optimizer makes the aggregation only once. If for instance the salary would somehow depend on employee department, then the AVG would be computed for each row. As a rule of thumb, if a subquery in the where clause, could be executed independently, it is executed only once at the beginning. –  ddaniel Apr 29 at 7:47

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.

share|improve this answer
    
Makes sense. Thank you. –  Patrick May 1 at 3:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.