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
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
db<>fiddle here