ORA-00934: Group function not allowed here || Sele

Question

O community, do you know how I could select the department_ID, and lowest salary of the department with the highest average salary? Or how to eliminate the'ORA-00934: group function not allowed here' issue? Would I need to use two subqueries?

So far, this is what I've come up with, trying to get the department_ID of the highest paid department:

SELECT department_ID, MIN(salary
FROM employees
WHERE department_ID = (SELECT department_ID
    FROM employees WHERE salary = MAX(salary));

Thank you, your assistance is greatly appreciated.

Answer 1

I can't test this, but it should work:

;WITH DepartmentsSalary AS
(
    SELECT department_ID, AVG(Salary) AvgSalary, MIN(Salary) MinSalary
    FROM employees
    GROUP BY department_ID
)
SELECT department_ID, MinSalary
FROM (  SELECT department_ID, AvgSalary, MAX(AvgSalary) OVER() MaxSalary, MinSalary
        FROM DepartmentsSalary) D
WHERE MaxSalary = AvgSalary

Answer 2

You can use join (then you have just one sub query)

select e1.department_ID, min(e1.salary)
from employees e1
join (
    select avg_query.department_ID, max(avg_query.avg_value) 
    from (
        select department_ID, avg(salary) as avg_value
        from employees
        group by department_ID
    ) avg_query
) e2 on e2.department_ID = e1.department_ID
;

• First sub-query returned average salary for all departments
• Next sub-query based on first sub-query returned highest average salary and related department_ID
• Main query returned min salary for department_ID with highest average salary