CODEWITHSUNDEEP
sqloracle-databaseaverage
Pugzly
Pugzly

Reputation: 934

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.

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

Upvotes: 0

Views: 418

Answers (2)

MT0
MT0

Reputation: 168001

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;

Or aggregate before joining the tables:

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

fiddle

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142743

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>

Upvotes: 1

Related Questions