Hello I am practicing in oracle's hr schema . I want to count avarage of maximum salaries grouped by countries and jobs.
when I use
avg(max(e.salary))
in case expression it returns not a single-group group function error
`select c.country_id AS "COUNTRY CODE",
e.job_id AS "JOB",
case
when max(e.salary)>avg(max(e.salary)) then 'HIGH SALARY'
else 'LOW SALARY'
end as info
from
hr.employees e right join hr.departments d on
e.department_id=d.department_id
right join hr.locations l on d.location_id=l.location_id
right join hr.countries c on l.country_id=c.country_id
group by c.country_id,e.job_id;`
but when I select it seperately it works fine :
`select avg(max(e.salary)) as "AVARAGE"
from hr.employees e right join hr.departments d on
e.department_id=d.department_id
right join hr.locations l on d.location_id=l.location_id
right join hr.countries c on l.country_id=c.country_id
group by c.country_id,e.job_id;`
can you explain me how to use avg(max()) functions together in case statement? thank you in advance.
Tim Biegeleisen :
One quick fix here might be to try using AVG() as an analytic function, over the entire aggregated table:\n\nselect\n c.country_id AS \"COUNTRY CODE\",\n e.job_id AS \"JOB\",\n case \n when max(e.salary) > avg(max(e.salary)) over ()\n then 'HIGH SALARY'\n else 'LOW SALARY'\n end as info\nfrom hr.employees e\nright join hr.departments d\n on e.department_id = d.department_id\nright join hr.locations l\n on d.location_id = l.location_id\nright join hr.countries c\n on l.country_id = c.country_id\ngroup by\n c.country_id,\n e.job_id;\n",
2019-08-13T05:15:24
Ed Bangga :
try this:\n\nselect t1.[COUNTRY_CODE], t1.JOB, case \n when t1.salary > avg(t1.salary) then 'HIGH SALARY'\n else 'LOW SALARY'\n end as info \nfrom (select c.country_id AS \"COUNTRY CODE\",\n e.job_id AS \"JOB\",\n max(e.salary) as salary\n from\n hr.employees e right join hr.departments d on \n e.department_id=d.department_id\n right join hr.locations l on d.location_id=l.location_id\n right join hr.countries c on l.country_id=c.country_id\n group by c.country_id,e.job_id) as t1;\ngroup by t1.[COUNTRY_CODE], t1.JOB, t1.salary\n",
2019-08-13T05:15:48
M Danish :
Nested aggravate function not allowed.\nYou should first get max salaries. Then again select on result with avg function.\nThanks, ",
2019-08-13T06:46:33