hello
Using the EMPLOYEES table for the HR schema, to find the avg salary group by job_id, I do
select job_id, avg(salary) from employees
group by job_id;
JOB_ID AVG(SALARY)
---------- -----------
AD_PRES 24000
AD_VP 17000
IT_PROG 5760
FI_MGR 12008
FI_ACCOUNT 7920
PU_MAN 11000
PU_CLERK 2780
ST_MAN 7280
ST_CLERK 2785
SA_MAN 12200
SA_REP 8350
SH_CLERK 3215
AD_ASST 4400
MK_MAN 13000
MK_REP 6000
HR_REP 6500
PR_REP 10000
AC_MGR 12008
AC_ACCOUNT 8300
If want display the maximum average salary group by job_id
select max(avg(salary)) from employees
group by job_id;
and I get
MAX(AVG(SALARY))
----------------
24000
However, if I want to see both the job_id and the max(avg(salary)) so that I know which job_id has the highest average salary like this
OB_ID AVG(SALARY)
---------- -----------
AD_PRES 24000
I use the following code.
select job_id, max(avg(salary)) from employees
group by job_id;
I would get an error ORA-00937: not a single-group group function.
Why is that not possible and how can I remedy this problem? Is subquery the only way to get both job_id and max(avg(salalry)) displayed together?