Oracle average salary for each department-CodePudding

I have the following code, which finds the average salary for each department and it works fine.

I'm having a problem adding department_name to the output. I know I have to JOIN the tables but I'm unable to get that to work.

Any help would be appreciated. Thanks to all who answer.


CREATE table dept  (department_id, department_name) AS
SELECT 1, 'IT' FROM DUAL UNION ALL
SELECT 2, 'SALES'  FROM DUAL;

CREATE TABLE employees (employee_id, manager_id, first_name, last_name, department_id, sal,
serial_number) AS
SELECT 1, NULL, 'Alice', 'Abbot', 1, 100000, 'D123' FROM DUAL UNION ALL
SELECT 2, 1, 'Beryl', 'Baron',1, 50000,'D124' FROM DUAL UNION ALL
SELECT 3, 1, 'Carol', 'Chang',1, 100000, 'A1424' FROM DUAL UNION ALL
SELECT 4, 2, 'Debra', 'Dunbar',1, 75000, 'A1425' FROM DUAL UNION ALL
SELECT 5, NULL, 'Emily', 'Eden',2, 90000, 'C1725' FROM DUAL UNION ALL
SELECT 6, 3, 'Fiona', 'Finn',1, 88500,'C1726' FROM DUAL UNION ALL
SELECT 7,5, 'Grace', 'Gelfenbein',2, 55000, 'C1727' FROM DUAL;
 
SELECT
    department_id,
    ROUND(AVG(sal), 2) AS AVERAGE_SALARY
FROM employees 
group by 
department_id;

Expected output

DEPARTMENT_ID DEPARTMENT_NAME AVERAGE_SALARY
1    IT         82700
2    SALES 72500

CodePudding user response：

Since there is only one department_name in each group then you can join the tables and use an aggregation function on the department_name:

SELECT e.department_id,
       MAX(d.department_name) AS department_name,
       ROUND(AVG(e.sal), 2) AS AVERAGE_SALARY
FROM   employees e
       INNER JOIN dept d
       ON (e.department_id = d.department_id)
GROUP BY e.department_id;

Or, you can aggregate and then join:

SELECT e.department_id,
       d.department_name,
       e.average_salary
FROM   (
         SELECT department_id,
                ROUND(AVG(sal), 2) AS average_salary
         FROM   employees
         GROUP BY department_id
       ) e
       INNER JOIN dept d
       ON (e.department_id = d.department_id);

Which, for your sample data, both output:

DEPARTMENT_ID DEPARTMENT_NAME AVERAGE_SALARY

2 SALES 72500

1 IT 82700

DEPARTMENT_ID	DEPARTMENT_NAME	AVERAGE_SALARY
2	SALES	72500
1	IT	82700

db<>fiddle here

CodePudding user response：

You can use a query like this:

SELECT
    d.department_id,
    d.department_name,
    ROUND(AVG(e.sal), 2) AS AVERAGE_SALARY
FROM employees e inner join dept d on e.department_id = d.department_id
group by 
d.department_id, d.department_name;

Here's an example: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=156f4e503bc3d6bfed3009137acacb23

Join the employees with department based on department_id.

When you do group by, include the 2 fields not included in the statistics. That'll give you statistics for the 2 fields you are looking for.

Your result will be

DEPARTMENT_ID | DEPARTMENT_NAME | AVERAGE_SALARY
------------: | :-------------- | -------------:
            2 | SALES           |          72500
            1 | IT              |          82700