average salary

• knikolov4521

  SSC Veteran

  Points: 272

  More actions

  January 17, 2015 at 11:22 am

  #300413

  Hello

  I would like to find the average salary for each department which has min salary

  In my case I will have 3 departments which have min salary.

  select distinct d.department_name, E.SALARY, avg(E.salary)

  FROM EMPLOYEES E JOIN DEPARTMENTS D

  ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID)

  WHERE E.SALARY = (SELECT MIN(E.SALARY)

  FROM EMPLOYEES E JOIN DEPARTMENTS D

  ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID))

  GROUP BY D.DEPARTMENT_NAME, E.SALARY;

• gulema.gerressu

  SSC Veteran

  Points: 208

  More actions

  January 18, 2015 at 8:59 am

  #1770996

  So that you don't have to query the table multiple times you can use the RANK function:

  SELECT salary,d.department_name FROM

  (

  SELECT e.salary,d.department_name,RANK() OVER(ORDER BY e.salary) myrank

  FROM employees e JOIN departments d ON e.department_id = d.department_id

  ) a

  WHERE myrank = 1