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]
  10. GO
  12. ALTER TABLE [dbo].[Employees] WITH CHECK ADD CONSTRAINT [FK_Employees_Departments] FOREIGN KEY([DepartmentID])
  13. REFERENCES [dbo].[Departments] ([DepartmentID])
  14. GO
  16. ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [FK_Employees_Departments]
  17. GO
  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]
  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.