ORA-00979: not a GROUP BY expression While trying to use MAX function

Question

When i wanted to add em_fname and emp_lname into my select statement. I kept getting NOT A GROUP BY EXPRESSION error.

The thing is when I add those 2 into GROUP BY clause, I got the unwanted query results (quite redundant).

Any suggestion on this?

select lgemployee.dept_num, emp_fname,emp_lname, max(sal_amount) as
HighestSalary 
from lgsalary_history inner join lgemployee on lgsalary_history.EMP_NUM = lgemployee.EMP_NUM  
group by lgemployee.dept_num;

See http://stackoverflow.com/questions/1520608/ora-00979-not-a-group-by-expression — John Flatness, Feb 16 '15 at 02:45

score 0 · Answer 1 · answered Feb 16 '15 at 02:45

0

add the none aggregated columns to group by condition, also it may required a left outer or right outer join (depends on your data)

select lgemployee.dept_num, emp_fname,emp_lname, max(sal_amount) as
HighestSalary 
from lgsalary_history inner join lgemployee on lgsalary_history.EMP_NUM = lgemployee.EMP_NUM  
group by lgemployee.dept_num,emp_fname,emp_lname;

answered Feb 16 '15 at 02:45

void

7,760
3
25
43

Thanks for the feedback. But I wanted to display the highest sal_amount from employee in each dept. if i remove emp_fname and emp_lname, I got 8 rows (from 8 departments), but if I add them as you suggested, I got 363 results (not what I wanted)! – Phuc Vinh Bui Feb 16 '15 at 02:51
So, plz show the structures and specify exactly what you want in the question, to give complete solution – void Feb 16 '15 at 02:52

score 0 · Answer 2 · answered Feb 16 '15 at 03:14

But I wanted to display the highest sal_amount from employee in each dept. if i remove emp_fname and emp_lname, I got 8 rows (from 8 departments), but if I add them as you suggested, I got 363 results (not what I wanted)!

It's hard to tell what your schema is like. Is sal_amount in lgsalary_history or lgemployee (I'm guessing the latter, but it's not clear as you don't use table aliases).

With this in mind, you probably want something like the following:

SELECT dept_num, emp_fname, emp_lname FROM (
    SELECT lge.dept_num, lge.emp_fname, lge.emp_lname
         , lgs.sal_amount
         , RANK() OVER ( PARTITION BY lge.dept_num ORDER BY lgs.sal_amount DESC) rn
      FROM lgemployee lge INNER JOIN lgsalary_history lgs
        ON lge.emp_num = lgs.emp_num
) WHERE rn = 1;

Now there is an added difficulty here, namely that if lgsalary_history is what I think it is, it will store past salaries in addition to current salaries. And it's just possible, even if unlikely, that a particular employee's salary could be cut. And the above query doesn't take that into account. So let's assume your salary history is dated, and there is a column dt in lgsalary_history that will allow is to determine the most recent salary for a given employee.

SELECT dept_num, emp_fname, emp_lname, sal_amount FROM (
    SELECT dept_num, emp_fname, emp_lname, sal_amount
         , RANK() OVER ( PARTITION BY dept_num ORDER BY sal_amount DESC ) AS rn
      FROM (
         SELECT e.dept_num, e.emp_fname, e.emp_lname, s.sal_amount, ROW_NUMBER() OVER ( PARTITION BY e.emp_num ORDER BY s.dt DESC ) AS rn
          FROM lgemployee e INNER JOIN lgsalary_history s
            ON e.emp_num = s.emp_num
    ) WHERE rn = 1
) WHERE rn = 1;

This will return all of the employees in a given department with the highest salary (so if there is more than one, it will return two, or three, etc.).

ORA-00979: not a GROUP BY expression While trying to use MAX function

2 Answers2