MySQL: get total number of employees in each department

I am new to MySQL and Have following question.

Let’s say I have two following tables

Departments

DepartmentId    Name

Employees

ID    DepartmentId   Name

I have written following query which is returning half of my expected result

select
  d.name as departmentName,
  count(*) as totalEmployees

from 
  employees e

join department d
on d.departmentId = e.departmentId

group by
  d.departmentId,
  d.name

which returns the result with number of employees in each department. but I want to have following two cases in my query.

1. Some departments don't have employees
2. Some departments have the same name

I have tried my query with NOT EXISTS and NOT IN but could not get expected result. Could anyone please help me here to cover above two cases in MySQL query.

Answer

I think you just need left join:

select d.departmentId, d.name as departmentName,
       count(e.departmentid) as totalEmployees
from department d left join
     employees e
     on d.departmentId = e.departmentId
group by d.departmentId, d.name;

Including the department id distinguishes among departments with the same name.

If you want all departments with the same name to be combined, then remove departmentid from the select and group by:

select d.name as departmentName,
       count(e.departmentid) as totalEmployees
from department d left join
     employees e
     on d.departmentId = e.departmentId
group by d.name;