CODEWITHSUNDEEP
mysqlsqlgroup-bycount
Programmer
Programmer

Reputation: 1294

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...

Upvotes: 0

Views: 1509

Answers (2)

Radim Bača
Radim Bača

Reputation: 10701

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.

Upvotes: 2

sanNeck
sanNeck

Reputation: 148

SELECT DeptName AS DepartmentName, COUNT(EmployeeID) AS NumberOfEmployees FROM Employee INNER JOIN Department ON DeptID = DepartmentID GROUP BY DepartmentID

Upvotes: 1

Related Questions