0

I have the following tables :

DEPARTMENT

department_id
department_name
manager_id
location_id

and

EMPLOYEES

employee_id
first_name
last_name
email
phone_number
hire_date
job_id
salary
commission_pct
manager_id
department_id

I want to count the number of employees working in each department.

I have used the following query :

SELECT department_name, count(*)
FROM departments d LEFT OUTER JOIN employees e
ON e.department_id = d.department_id
GROUP BY department_name;

But in the results the employee which has no employees does not show up in the results. I want the number of employees to show up as 0. How do I do that?

2 Answers 2

2

In terms of the speed:

As I know that both the queries (select count(*) or Select Count(id)) are using same execution plan, so should use same amount of CPU as long as blocks are fetched from either buffer pool or from disk not mix.

In terms of the result, and in your case:

  • if you said count(*), then it will count the row even if you don't have employees since you are making left join.
  • if you said count(employee_id), then this will only count the rows "which are exists", means not the null rows.. I think this is what you need to have 0 for your case

  • If you want to mention the field and you want to count it even if there's no row you have to say count(NVL(employee_id, 123)) while 123 is any value in case null

2
  • Ok, understood, is there any other way to write this query without a LEFT JOIN? Mar 17, 2014 at 7:16
  • Yes, try this: SELECT DEPARTMENT_NAME, (SELECT COUNT (EMPLOYEE_ID) FROM EMPLOYEES WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID) AS COUNT_OF_EMPS FROM DEPARTMENTS
    – Adel
    Mar 17, 2014 at 7:21
1

I want the number of employees to show up as 0. How do I do that?

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

count(*) will count all rows and count(e.employee_id) counts all non null values.

SQL Fiddle

But in the results the employee which has no employees does not show up in the results.

I have no idea why you see that. The query you have returns a count of 1 for the departments without employees.

SQL Fiddle

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.