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

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

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.

CREATE TABLE [dbo].[Employees](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NULL,
    [Position] [nvarchar](50) NULL,
    [Office] [nvarchar](50) NULL,
    [Salary] [int] NULL,
    [DepartmentID] [int] NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [FK_Employees_Departments] FOREIGN KEY([DepartmentID])
REFERENCES [dbo].[Departments] ([DepartmentID])
GO

ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [FK_Employees_Departments]
GO

CREATE TABLE [dbo].[Departments](
    [DepartmentID] [int] IDENTITY(1,1) NOT NULL,
    [DepartmentName] [nvarchar](50) NULL,
 CONSTRAINT [PK_Departments] PRIMARY KEY CLUSTERED
(
    [DepartmentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

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

SELECT DEPARTMENTNAME,COUNT (*) AS EMPLOYEECOUNT
FROM EMPLOYEES
JOIN DEPARTMENTS
ON EMPLOYEES.DEPARTMENTID=DEPARTMENTS.DEPARTMENTID
GROUP BY DEPARTMENTNAME

 

Example 2

SELECT DEPARTMENTNAME,COUNT(*) AS EMPLOYEECOUNT
FROM EMPLOYEES
JOIN DEPARTMENTS
ON EMPLOYEES.DEPARTMENTID=DEPARTMENTS.DEPARTMENTID
GROUP BY DEPARTMENTNAME
ORDER BY EMPLOYEECOUNT DESC

 

 

Example 3

SELECT TOP 1 DEPARTMENTNAME
FROM EMPLOYEES
JOIN DEPARTMENTS
ON EMPLOYEES.DEPARTMENTID=DEPARTMENTS.DEPARTMENTID
GROUP BY DEPARTMENTNAME
ORDER BY COUNT (*) DESC

 

OR

SELECT TOP 1 DEPARTMENTNAME COUNT (*) AS EMPLOYEECOUNT
FROM EMPLOYEES
JOIN DEPARTMENTS
ON EMPLOYEES.DEPARTMENTID=DEPARTMENTS.DEPARTMENTID
GROUP BY DEPARTMENTNAME
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.