Finding list of max average salary, department wise, whilst displaying both max average salary and d-CodePudding

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.

CodePudding user response：

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