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  
SQL Server

SQL Server
 
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  
SQL Server 

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  
SQL Server 
 
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  
SQL Server 

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  
 SQL Server

Conclusion

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

X

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now