Displaying Department Name Having Least 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 least number of employees working in it. We shall use the TOP, COUNT, ORDER BY, and GROUP BY 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 having the least 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 increasing order using ORDER BY. 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 least number of employees as MIN_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(*);

Query:

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

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

Output:

Method #2: Using Subquery

SELECT department_name
FROM employees
GROUP BY department_name
HAVING COUNT(*) = (SELECT MIN(emp_count) FROM (SELECT COUNT(*) AS emp_count FROM employees GROUP BY department_name) AS counts)

Method 3: 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 DEPTNAME, EmployeeCount
FROM EmployeeCounts
WHERE EmployeeCount = (SELECT MIN(EmployeeCount) FROM EmployeeCounts);

Output:

sql output