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 w3wiki.
Query:
CREATE DATABASE w3wiki
Output:
Step 2: Use the w3wiki database. For this use the below command.
Query:
USE w3wiki
Output:
Step 3: Create a table COMPANY inside the database w3wiki. 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: