# How To Find The Highest Number Of Employees In a Department Using SQL Server

Introduction

In this blog, I will demonstrate how to find the highest number of employees in a particular department using SQL server 2014.

Step 1

Create two tables, employees and department table, in SQL server.

1. CREATE TABLE [dbo].[Employees](
2.     [ID] [int] IDENTITY(1,1) NOT NULL,
3.     [Name] [nvarchar](50) NULL,
4.     [Position] [nvarchar](50) NULL,
5.     [Office] [nvarchar](50) NULL,
6.     [Salary] [int] NULL,
7.     [DepartmentID] [int] NULL
8. ) ON [PRIMARY]
9.
10. GO
11.
12. ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [FK_Employees_Departments] FOREIGN KEY([DepartmentID])
13. REFERENCES [dbo].[Departments] ([DepartmentID])
14. GO
15.
16. ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [FK_Employees_Departments]
17. GO
18.
19. CREATE TABLE [dbo].[Departments](
20.     [DepartmentID] [int] IDENTITY(1,1) NOT NULL,
21.     [DepartmentName] [nvarchar](50) NULL,
22.  CONSTRAINT [PK_Departments] PRIMARY KEY CLUSTERED
23. (
24.     [DepartmentID] ASC
25. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
26. ) ON [PRIMARY]
27.
28. GO

Step 2

Make a relationship between both tables and insert some data into the tables. Write a query to find out the highest number of employees in the department.

Example 1

1. SELECT DEPARTMENTNAME,COUNT (*) AS EMPLOYEECOUNT
2. FROM EMPLOYEES
3. JOIN DEPARTMENTS
4. ON EMPLOYEES.DEPARTMENTID=DEPARTMENTS.DEPARTMENTID
5. GROUP BY DEPARTMENTNAME

Example 2

1. SELECT DEPARTMENTNAME,COUNT(*) AS EMPLOYEECOUNT
2. FROM EMPLOYEES
3. JOIN DEPARTMENTS
4. ON EMPLOYEES.DEPARTMENTID=DEPARTMENTS.DEPARTMENTID
5. GROUP BY DEPARTMENTNAME
6. ORDER BY EMPLOYEECOUNT DESC

Example 3
1. SELECT TOP 1 DEPARTMENTNAME
2. FROM EMPLOYEES
3. JOIN DEPARTMENTS
4. ON EMPLOYEES.DEPARTMENTID=DEPARTMENTS.DEPARTMENTID
5. GROUP BY DEPARTMENTNAME
6. ORDER BY COUNT (*) DESC

OR

1. SELECT TOP 1 DEPARTMENTNAME COUNT (*) AS EMPLOYEECOUNT
2. FROM EMPLOYEES
3. JOIN DEPARTMENTS
4. ON EMPLOYEES.DEPARTMENTID=DEPARTMENTS.DEPARTMENTID
5. GROUP BY DEPARTMENTNAME
6. ORDER BY EMPLOYEECOUNT DESC

Conclusion

In this blog, I have explained how to find the number of employees in a department with different examples step by step.