Forum Stats

  • 3,875,419 Users
  • 2,266,914 Discussions
  • 7,912,203 Comments

Discussions

Average salary for each department

Pugzly
Pugzly Member Posts: 61 Green Ribbon

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


Tagged:

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,780 Red Diamond
    edited Nov 10, 2022 2:29PM Answer ✓

    Hi, @User_G3NSO

    You can use the aggregate COUNT function, like this:

    SELECT    d.department_id
    , 	  d.department_name
    , 	  ROUND (AVG (e.salary))  AS avg_sal
    ,	  COUNT (*)  		  AS num_employees
    FROM 	  departments d
    JOIN 	  employees   e  ON  e.department_id = d.department_id
    GROUP BY  d.department_id, d.department_name
    ORDER BY  d.department_id;
    

    COUNT (col)    returns the number of rows that have a non-NULL col

    COUNT (*)      returns the number of rows, regardless of what columns are NULL

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,780 Red Diamond
    edited Nov 10, 2022 2:29PM Answer ✓

    Hi, @User_G3NSO

    You can use the aggregate COUNT function, like this:

    SELECT    d.department_id
    , 	  d.department_name
    , 	  ROUND (AVG (e.salary))  AS avg_sal
    ,	  COUNT (*)  		  AS num_employees
    FROM 	  departments d
    JOIN 	  employees   e  ON  e.department_id = d.department_id
    GROUP BY  d.department_id, d.department_name
    ORDER BY  d.department_id;
    

    COUNT (col)    returns the number of rows that have a non-NULL col

    COUNT (*)      returns the number of rows, regardless of what columns are NULL

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,951 Red Diamond

    Just add COUNT(*)

    SELECT d.department_id, d.department_name, ROUND (AVG (e.salary)) avg_sal,COUNT(*) NUM_EMPLOYEES
            FROM departments d
                JOIN employees e ON e.department_id = d.department_id
        GROUP BY d.department_id, d.department_name
    ORDER BY 1;
    


    SY.

  • mathguy
    mathguy Member Posts: 10,912 Black Diamond

    What is wrong with what you have? You didn't explain what issue you are having.

    Is it that you must, now, show all departments, including those without employees?

    If so, change the JOIN to LEFT OUTER JOIN; and for the NUM_EMPLOYEES column, add COUNT(*) AS NUM_EMPLOYEES to the SELECT clause.