novice novice - 28 days ago 11
MySQL Question

How to use COUNT() and GROUP BY to display number of employees in each department

Please consider the two tables - Employee and Department

Employee:
EmployeeID
Name
DeptID

Department:
DepartmentID
DeptName
LocID

Employee.DeptID is a foreign key to Department.DepartmentID


How would one display a list of all departments (Department Names) and the number of employees in each department? The output should look like this:

DepartmentName Number of employees
Accounts 30
HR 24
Production 400
Sales/Marketing 250

etc...

Answer Source

Use GROUP BY

 SELECT d.deptID, count(e.deptID)
 FROM Department d
 LEFT JOIN Employee e ON d.DeptID = e.DeptID
 GROUP BY d.deptId

and LEFT JOIN is used to include departments that do not have employees.