CODEWITHSUNDEEP
oracle-database
Vaiebhav Patil
Vaiebhav Patil

Reputation: 15

Finding list of max average salary, department wise, whilst displaying both max average salary and department_id

When I try it without displaying department_id it works fine as :

SQL> SELECT MAX(AVG(SALARY)) FROM EMPLOYEE GROUP BY DEPARTMENT_ID;

MAX(AVG(SALARY))
----------------
          800000

But when I want to display the department_id's too, it gives me error as follows:

SQL> SELECT DEPARTMENT_ID, MAX(AVG(SALARY)) FROM EMPLOYEE GROUP BY DEPARTMENT_ID;
SELECT DEPARTMENT_ID, MAX(AVG(SALARY)) FROM EMPLOYEE GROUP BY DEPARTMENT_ID
       *
ERROR at line 1:
ORA-00937: not a single-group group function

Is there any explanation for this? What am I doing wrong? I went through answers of previous questions like this and tried their solutions but got same or some other error. Any help would be appreciated.

Upvotes: 0

Views: 401

Answers (1)

Littlefoot
Littlefoot

Reputation: 142958

I suggest you use

SELECT department_id, avg_salary
  FROM (  SELECT DEPARTMENT_ID,
                 AVG (SALARY) avg_salary,
                 RANK () OVER (ORDER BY AVG (salary) DESC) rnk
            FROM EMPLOYEE
        GROUP BY DEPARTMENT_ID)
 WHERE rnk = 1;

i.e.

  • use your first query as a "source"
    • additionally, rank average salaries in descending order (using the rank analytic function)
  • select row which ranks as highest

Upvotes: 1

Related Questions