Displaying Department Name Having Most Number of Employees in SQL Server

In SQL, we need to find out the department-wise information from the given table containing information about employees. One such data is the name of the department having the most number of employees working in it. We shall use the TOP, COUNT, ORDER BY, GROUP BY, and DESC clauses to achieve this. This is illustrated below. For this article, we will be using the Microsoft SQL Server as our database.

Step 1: Create a Database. For this use the below command to create a database named GeeksForGeeks.

Query:

CREATE DATABASE GeeksForGeeks

Output:

Step 2: Use the GeeksForGeeks database. For this use the below command.

Query:

USE GeeksForGeeks

Output:

Step 3: Create a table COMPANY inside the database GeeksForGeeks. This table has 4 columns namely EMPLOYEE_ID, EMPLOYEE_NAME, DEPARTMENT_NAME, and SALARY containing the id, name, department, and the salary of various employees.

Query:

CREATE TABLE COMPANY(
EMPLOYEE_ID INT PRIMARY KEY,
EMPLOYEE_NAME VARCHAR(10),
DEPARTMENT_NAME VARCHAR(10),
SALARY INT);

Output:

Step 4: Describe the structure of the table COMPANY.

Query:

EXEC SP_COLUMNS COMPANY;

Output:

Step 5: Insert 5 rows into the COMPANY table.

Query:

INSERT INTO COMPANY VALUES(1,'RAM','HR',10000);
INSERT INTO COMPANY VALUES(2,'AMRIT','MRKT',20000);
INSERT INTO COMPANY VALUES(3,'RAVI','HR',30000);
INSERT INTO COMPANY VALUES(4,'NITIN','MRKT',40000);
INSERT INTO COMPANY VALUES(5,'VARUN','IT',50000);

Output:

Step 6: Display all the rows of the COMPANY table.

Query:

SELECT * FROM COMPANY;

Output:

Step 7: Display the name of the department that has the highest number the employees working in it. We will use the aggregate function COUNT here to find the number of employees working in each department. This is further achieved using the GROUP BY clause to count the number department-wise. Then all such obtained frequencies are sorted in decreasing order using ORDER BY and DESC is used to specify the descending order. Then the topmost row is selected using the TOP clause followed 1. 1 here indicates that the query returns only 1 row. We also name the new column of the most number of employees as MAX_NO_OF_EMPLOYEES using the AS clause which creates kind of an alias.

Syntax:

SELECT TOP 1 COLUMN1, COUNT(*) AS ALIAS
FROM TABLE_NAME
GROUP BY COLUMN1
ORDER BY COUNT(*) DESC;

Query:

SELECT TOP 1 DEPARTMENT_NAME, COUNT(*) AS MAX_NO_OF_EMPLOYEES
FROM COMPANY
GROUP BY DEPARTMENT_NAME
ORDER BY COUNT(*) DESC;

Note – This query returns only 1 row i.e. the topmost row among the returned sorted rows.

Output:

Method 2: Using a Common Table Expression (CTE)

A common table (CTE) is a temporary result set that  can be referenced in SQL statements. This allows you to define  named queries that can be used repeatedly within  query blocks, making complex queries easier to read and maintain. 

Syntax:

WITH CTE_Name (column1, column2, ..., columnN) AS (
    -- CTE query definition here
    SELECT ...
    FROM ...
    WHERE ...
)
SELECT ...
FROM ...
JOIN CTE_Name ON ...
WHERE ...

Query:

WITH EmployeeCounts AS (
    SELECT DEPTNAME, COUNT(*) AS EmployeeCount
    FROM DEPARTMENT
    GROUP BY DEPTNAME
)
SELECT top 1 DEPTNAME, EmployeeCount
FROM EmployeeCounts
WHERE EmployeeCount = (SELECT MAX(EmployeeCount) FROM EmployeeCounts);

Output:

Sql Output