CODEWITHSUNDEEP
sqloracle-database
Liana
Liana

Reputation: 7

ORACLE SQL: Show the lowest salary in the department with the highest average salary

NOTE, this is a homework question.

Please show the department number and the lowest salary in the department whose average salary is the highest average salary.

This is what I have so far,

SELECT DEPARTMENT_ID, MAX_AVG_SALARY
FROM
  (SELECT DEPARTMENT_ID, AVG(SALARY) AS MAX_AVG_SALARY
  FROM EMPLOYEES
  GROUP BY DEPARTMENT_ID)
WHERE MAX_AVG_SALARY =
  (SELECT MAX(MAX_AVG_SALARY)
  FROM
    (SELECT DEPARTMENT_ID,
      AVG(SALARY) AS MAX_AVG_SALARY
    FROM EMPLOYEES
    GROUP BY DEPARTMENT_ID
    ));

I can get the department_id with the highest salary, but then how do I find the lowest salary in the same department?

Please help!

Thanks!

Upvotes: 0

Views: 7499

Answers (7)

Pavan
Pavan

Reputation: 16

This is the answer without using row limiting features as old oracle does not provide the row limiting feature

SELECT job_id, avg(salary) FROM Employees 
GROUP BY job_id 
HAVING AVG(salary) = (SELECT MIN(AVG(salary)) FROM EMPLOYEES GROUP BY job_id);

Upvotes: 0

Aditya G
Aditya G

Reputation: 11

WITH CTE(ID, SAL) AS 
(
   SELECT DISTINCT
      DEPARTMENT_ID,
      AVG(SALARY) OVER(PARTITION BY DEPARTMENT_ID)"AVGSAL" 
   FROM
      EMPLOYEES 
   ORDER BY
      AVGSAL DESC
)
SELECT
   MIN(SALARY) 
FROM
   EMPLOYEES E1 
WHERE
   E1.DEPARTMENT_ID = 
   (
      SELECT
         ID 
      FROM
         CTE 
      WHERE
         ROWNUM = 1
   )
;

Upvotes: 0

As Sabir
As Sabir

Reputation: 1

select e.department_id, min(e.salary) min
  from employees e
having avg(e.salary) = (select max(avg(e.salary))
                          from employees e
                         group by e.department_id)
 group by e.department_id

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

I would solve this using analytic functions, particularly row_number() to get the department with the highest average salary:

select department_id, mins 
from (select department_id, avg(salary) as avgs, min(salary) as mins,
             row_number() over (order by avg(salary) desc) as seqnum
      from employees
      group by department_id
    ) de
where seqnum = 1;

Upvotes: 0

MT0
MT0

Reputation: 167962

You can use a named subquery that you can reuse:

WITH grouped_salaries AS (
  SELECT department_id,
         MIN( salary ) AS min_dept_salary,
         AVG( salary ) AS avg_dept_salary
  FROM   Employees
  GROUP BY department_id
)
SELECT department_id,
       min_dept_salary
FROM   grouped_salaries
WHERE  avg_dept_salary = ( SELECT MAX( avg_dept_salary )
                           FROM   grouped_salaries );

Upvotes: 0

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

Solution with analytic functions:

select department_id, ms min_salary 
  from (
    select department_id, max(avg(salary)) over () mav,
        min(min(salary)) over (partition by department_id) ms, 
        min(avg(salary)) over (partition by department_id) av 
      from employees group by department_id )
  where av = mav order by department_id

SQLFiddle demo

Upvotes: 0

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

SELECT MINIMUM_SALARY,DEPARTMENT_ID
FROM
(
 SELECT AVG(SALARY) AS AVERAGE_SALARY,
        MIN(SALARY) AS MINIMUM_SALARY,
        DEPARTMENT_ID
  FROM EMPLOYEES
 GROUP BY DEPARTMENT_ID
)EMPLOYEE_AGGREGATED
WHERE
    AVERAGE_SALARY = (SELECT MAX(AVG(SALARY)) FROM EMPLOYEES GROUP BY DEPARTMENT_ID)

Upvotes: 1

Related Questions