Number of employees for each department

Question

I am trying to find the number of employees for each department. I have the following setup and code, which almost works.

As you can see I have 4 departments but only 3 of them (1,2,4) have employees. How can I get department 3 in my output that shows 0 employees.


CREATE TABLE departments(  department_id, department_name) AS
SELECT 1, 'IT' FROM DUAL UNION ALL
SELECT 2, 'Sales' FROM DUAL UNION ALL
SELECT 3, 'Marketing' FROM DUAL UNION ALL
SELECT 4, 'Finance' 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 '2011-08-04', 150200, 1 FROM DUAL UNION ALL
SELECT 3, 'Beth', 'Cooper', DATE '2019-03-05', 60700, 1 FROM DUAL UNION ALL
SELECT 4, 'Carol', 'Orr', DATE '2007-11-11', 70125,1 FROM DUAL UNION ALL
SELECT 5, 'Vicky', 'Palazzo', DATE '2004-09-17', 68525,2 FROM DUAL UNION ALL
SELECT 6, 'Cheryl', 'Ford', DATE '2020-05-10', 110000,1 FROM DUAL UNION ALL
SELECT 7, 'Leslee', 'Altman', DATE '2008-12-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 UNION ALL
SELECT 10, 'Silvio', 'Dante', DATE '2022-10-16', 102150,4 FROM DUAL UNION ALL
SELECT 11, 'Jerry', 'Torchiano', DATE '2022-10-30', 112660,4 FROM DUAL;

select 
  d.department_id,
  d.department_name, 
 count(*) as cnt
 from departments d
 INNER join employees e  on e.department_id = d.department_id 
 group by
   d.department_id,
   d.department_name
ORDER BY d.department_id;

Department_Id Department_name CNT
1   IT       6
2   Sales    3
4   Finance  2

Justin Cave · Accepted Answer · 2023-07-16T18:10:27.260

1

Use a left outer join, not an inner join. And count the e.eployee_id.

select 
  d.department_id,
  d.department_name, 
 count(e.employee_id) as cnt
 from departments d
 left outer join employees e  on e.department_id = d.department_id 
 group by
   d.department_id,
   d.department_name
ORDER BY d.department_id;

See this sqlFiddle. This is likely as close to your original query as you can get and still get the same answer. You could also write this as

select 
  d.department_id,
  d.department_name, 
  (select count(*)
     from employees e 
    where e.department_id = d.department_id) as cnt
 from departments d
 group by
   d.department_id,
   d.department_name
ORDER BY d.department_id;

or

select 
  d.department_id,
  d.department_name, 
  nvl( e.cnt, 0) as cnt
 from departments d
 left outer join (select e.department_id, count(*) cnt
                    from employees e
                   group by e.department_id) e
              on d.department_id = e.department_id
ORDER BY d.department_id;

though either of those are likely further away from your original query.

edited Jul 16 '23 at 18:10

answered Jul 16 '23 at 17:22

Justin Cave

227,342
24
367
384

Thank you. While your solution works perfectly I am curious and for academic purposes are there other ways to solve this problem? – Pugzly Jul 16 '23 at 17:55
@Pugzly - Sure. There are generally dozens of ways to write a query to get the same results. Is there a particular method you would like to use? – Justin Cave Jul 16 '23 at 17:57
If it isn't too much trouble, I would like to see something close to my original attempt (no count(e.emplyee_id)) to understand where I went wrong – Pugzly Jul 16 '23 at 18:01
@Pugzly - That's likely as close to your original query as you can get while getting the answer you want. But I added a couple other ways to get the same result that use a `count(*)` rather than counting the `e.employee_id`. – Justin Cave Jul 16 '23 at 18:11

Number of employees for each department

1 Answers1