Average salary for each department-CodePudding

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

CodePudding user response：

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

CodePudding user response：

You need inner join and avg aggregation function (not in its analytic form!):

SQL>   SELECT d.department_id, d.department_name, ROUND (AVG (e.salary)) avg_sal
  2      FROM departments d
  3            JOIN employees e ON e.department_id = d.department_id
  4  GROUP BY d.department_id, d.department_name;

DEPARTMENT_ID DEPAR    AVG_SAL
------------- ----- ----------
            1 IT         76111
            2 DBA       133333

SQL>

In case you'd want to display department(s) with no employees, you'd use outer join:

SQL>   SELECT d.department_id, d.department_name, ROUND (AVG (e.salary)) avg_sal
  2      FROM departments d
  3           LEFT JOIN employees e ON e.department_id = d.department_id
  4  GROUP BY d.department_id, d.department_name;

DEPARTMENT_ID DEPAR    AVG_SAL
------------- ----- ----------
            1 IT         76111
            3 Sales
            2 DBA       133333

SQL>