ORA-00934: Group function not allowed here || Selecting MIN(Salary) of highest paid dept

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.

http://psoug.org/reference/group_by.html – Aprillion Mar 14 '12 at 18:15 — Aprillion, Mar 14 '12 at 18:15

Lamak · Accepted Answer · 2012-03-14T18:35:39.027

0

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

edited Mar 14 '12 at 18:35

answered Mar 14 '12 at 18:13

Lamak

69,480
12
108
116

I know you mentioned you can't test the issue, but the solution didn't work. Returned: Error at Line 10: ORA-00904: "AVGSALARY": invalid identifier. – Mar 14 '12 at 18:30
1

@TylerJFisher - You are right. I updated my answer, let me know if it helps – Lamak Mar 14 '12 at 18:36
Oracle needs the statement termination character (the `;`) at the *end*, not at the beginning of the statement. – Mar 14 '12 at 18:38

Matusz · Answer 2 · 2012-03-14T20:16:22.213

0

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

edited Mar 14 '12 at 20:16

answered Mar 14 '12 at 18:58

Matusz

44
4

Your first solution just takes the employee with the max salary, not the department with the max average salary of all. Your second query doesn't choose the department with the max salary either, just takes every department and calculates the max salary of each – Lamak Mar 14 '12 at 19:05
I forgot about 'highest average salary'. Now second query return correct result. If you put 'select a, max(b) from c' without 'group by' query returns only one row. I removed first query because of this fragment: FROM employees WHERE salary = (SELECT MAX(avg_dataSet.avg_salary) – Matusz Mar 14 '12 at 20:19

ORA-00934: Group function not allowed here || Selecting MIN(Salary) of highest paid dept

2 Answers2

Linked