# Average salary for each department

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.

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