SQL Interview Questions · Question 02

Write a query to find the number of employees in each department?

Interview preparation resource from Gate Smashers.

Interview-ready answer

SELECT department, COUNT(*) AS employee_count FROM Employees GROUP BY department;

Employees

employee_id	employee_name	department	salary	manager_id
101	Aarav	Engineering	95000	105
102	Meera	Engineering	72000	105
103	Kabir	Sales	65000	106
104	Isha	Sales	92000	106
105	Rohan	Engineering	90000	NULL
106	Neha	Sales	88000	NULL

SQL querysql

SELECT department, COUNT(*) AS employee_count
FROM Employees
GROUP BY department;

Understand it clearly

Direct query

Group employees by the department and count rows per group. The query to use is shown in the code block below; it returns one row per department that exists in the Employees table, with the number of employees in that department.

How it works

GROUP BY creates one group for every distinct department value in the Employees table. COUNT(*) then counts every row in each group, producing the employee count for that department.

GROUP BY: Produces one group per distinct department value.
**COUNT(*):** Counts every row in each group (includes rows even if unrelated columns are NULL).

Departments with no employees

If you need to show departments that have zero employees, start from a Departments table and LEFT JOIN Employees so that departments without matching employees still appear with a count of zero (the supplied query groups only rows present in Employees).

When to use: Use a LEFT JOIN from Departments to Employees when you must include departments that currently have no employees.

NULL handling

COUNT(*) counts rows regardless of NULL values in other columns. That means rows with NULLs in unrelated columns still contribute to the department count.

Quick comparison

BasisGrouping from Employees (only departments present in Employees)Including empty departments (Departments LEFT JOIN Employees)

Result includes departments with no employeesNo — only departments that appear in Employees are returnedYes — starting from Departments and LEFT JOIN will keep departments with zero employees

Requires a Departments tableNoYes — you must have a Departments table to include empty departments

Counting behaviorCOUNT(*) counts every row in each group, including rows with NULLs in unrelated columnsSame counting behavior for matched/returned rows; unmatched departments will show zero when you COUNT(Employees.employee_id) or use COALESCE on the count

Direct query

How it works

Departments with no employees

NULL handling

Related SQL Interview Questions questions