I got the average salary for each department, which appears to be working well. Note since Department 3 has no employees there is no row. This is what I wanted.
I like to append another column to each row num_employees (this count should be for each department). This is where I'm stuck and was hoping someone could help me out.
Below is my test CASE. Thanks in advance to all who answer and your expertise.
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', 160000, 1 FROM DUAL UNION ALL
SELECT 2, 'Sandy', 'Herring', DATE '2001-04-04', 150200, 1 FROM DUAL UNION ALL
SELECT 3, 'Beth', 'Cooper', DATE '2001-04-05', 60700, 1 FROM DUAL UNION ALL
SELECT 4, 'Carol', 'Orr', DATE '2001-04-06', 70125,1 FROM DUAL UNION ALL
SELECT 5, 'Vicky', 'Palazzo', DATE '2001-04-07', 68525,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;
/* avg salary for department */
SELECT d.department_id, d.department_name, ROUND (AVG (e.salary)) avg_sal
FROM departments d
JOIN employees e ON e.department_id = d.department_id
GROUP BY d.department_id, d.department_name
ORDER BY 1;
Expected output
DEPARTMENT_ID DEPARTMENT_NAME AVG_SAL NUM_EMPLOYEES
1 IT 102949 6
2 DBA 126842 3