Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Average salary for each department

PugzlyNov 10 2022

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
This post has been answered by Frank Kulash on Nov 10 2022
Jump to Answer
Comments
Post Details
Added on Nov 10 2022
3 comments
4,110 views