Average salary for each department

November 10, 2022

I’m trying to get the average employee salary for each department. I am expecting two rows because one department doesn’t have any employees assigned to it.

Can someone please tell me how to rectify this issue. Thanks in advance to all who answer and your expertise.

Below is my test CASE and inaccurate result.


CREATE TABLE departments(  department_id, department_name) AS
SELECT 1, 'IT' FROM DUAL UNION ALL
SELECT 3, 'Sales' FROM DUAL UNION ALL
SELECT 2, 'DBA' FROM DUAL;

CREATE TABLE employees (employee_id, first_name, last_name, hire_date, salary,  department_id) AS
SELECT 1, 'Lisa', 'Saladino', DATE '2001-04-03', 100000, 1 FROM DUAL UNION ALL
SELECT 2, 'Abby', 'Abbott', DATE '2001-04-04', 50000, 1 FROM DUAL UNION ALL
SELECT 3, 'Beth', 'Cooper', DATE '2001-04-05', 60000, 1 FROM DUAL UNION ALL
SELECT 4, 'Carol', 'Orr', DATE '2001-04-06', 70000,1 FROM DUAL UNION ALL
SELECT 5, 'Vicky', 'Palazzo', DATE '2001-04-07', 88000,2 FROM DUAL UNION ALL
SELECT 6, 'Cheryl', 'Ford', DATE '2001-04-08', 110000,1 FROM DUAL UNION ALL
SELECT 7, 'Leslee', 'Altman', DATE '2001-04-10', 66666, 1 FROM DUAL UNION ALL
SELECT 8, 'Jill', 'Coralnick', DATE '2001-04-11', 190000, 2 FROM DUAL UNION ALL
SELECT 9, 'Faith', 'Aaron', DATE '2001-04-17', 122000,2 FROM DUAL;

SELECT d.department_id,
         d.department_name,
         round(avg(e.salary) over (partition by e.department_id)) avg_sal
  FROM   departments d
         JOIN employees e
         ON (d.department_id = e.department_id)


DEPARTMENT_ID    DEPARTMENT_NAME    AVG_SAL
1    IT    76111
1    IT    76111
1    IT    76111
1    IT    76111
1    IT    76111
1    IT    76111
2    DBA    133333
2    DBA    133333
2    DBA    133333

>Solution :

Use the AVG aggregation function rather than analytic function and aggregate by the primary key for the department:

SELECT d.department_id,
       MAX(d.department_name) AS department_name,
       ROUND(AVG(e.salary)) avg_sal
FROM   departments d
       INNER JOIN employees e
       ON (d.department_id = e.department_id)
GROUP BY
       d.department_id;

Or, you can just aggregate the employees table and use a correlated sub-query to get the name:

SELECT department_id,
       ( SELECT department_name
         FROM   departments d
         WHERE  d.department_id = e.department_id ) AS department_name,
       ROUND(AVG(salary)) avg_sal
FROM   employees e
GROUP BY
       department_id;

fiddle