Definition
- JOIN + GROUP BY + COUNT – The standard pattern: join brings department names next to employees, GROUP BY defines the buckets, COUNT tallies per bucket.
- LEFT JOIN – Ensures departments with zero employees still appear in the result (INNER JOIN drops them).
COUNT(e.emp_id)– Counts the employee key column, which is NULL for empty departments and correctly reports 0.- **
COUNT(*)** – Counts all rows including the NULL placeholder, wrongly reporting 1 for an empty department. - GROUP BY primary key – Group by
dept_id(not justdept_name) because names could theoretically repeat. - HAVING – Filters groups after aggregation, e.g.,
HAVING COUNT(e.emp_id) > 10for departments with more than 10 people.
At National Bank of India, Priya in HR wants a headcount per department. She lines up all employee badges, sorts them into department piles (GROUP BY), and counts each pile (COUNT). If the brand-new Crypto Desk has no staff yet, a LEFT JOIN still lists it, and counting employee IDs (not rows) correctly shows zero.
Understanding the Concepts
How do you count employees per department with INNER JOIN?
The simplest version counts only departments that have at least one employee. An INNER JOIN drops departments with no matching employees.
-- INNER JOIN: departments with at least one employee
SELECT d.dept_name, COUNT(*) AS employee_count
FROM department d
JOIN employees e ON e.dept_id = d.dept_id
GROUP BY d.dept_id, d.dept_name
ORDER BY employee_count DESC;
Expected output:
dept_name | employee_count
-------------+---------------
Loans | 25
Operations | 18
Fraud | 12
-- Crypto Desk (0 employees) does NOT appear
How do you include departments with zero employees?
Use a LEFT JOIN from the department side and count the employee's primary key column, not *.
-- LEFT JOIN: all departments including empty ones
SELECT d.dept_name, COUNT(e.emp_id) AS employee_count
FROM department d
LEFT JOIN employees e ON e.dept_id = d.dept_id
GROUP BY d.dept_id, d.dept_name
ORDER BY employee_count DESC;
Expected output: dept_name | employee_count -------------+--------------- Loans | 25 Operations | 18 Fraud | 12 Crypto Desk | 0 ← correctly shows zero
Why is COUNT(e.emp_id) important instead of COUNT(*)?
A LEFT JOIN manufactures a single NULL-filled row for a department with no employees. COUNT(*) counts that row and wrongly reports 1. COUNT(e.emp_id) evaluates the employee key, which is NULL on the placeholder row, and correctly reports 0.
-- WRONG: COUNT(*) reports 1 for empty department
SELECT d.dept_name, COUNT(*) AS employee_count
FROM department d
LEFT JOIN employees e ON e.dept_id = d.dept_id
GROUP BY d.dept_id, d.dept_name;
-- Crypto Desk: 1 ← WRONG
-- CORRECT: COUNT(e.emp_id) reports 0 for empty department
SELECT d.dept_name, COUNT(e.emp_id) AS employee_count
FROM department d
LEFT JOIN employees e ON e.dept_id = d.dept_id
GROUP BY d.dept_id, d.dept_name;
-- Crypto Desk: 0 ← CORRECT
Why group by dept_id and not just dept_name?
Two different departments could theoretically share a display name. Grouping by the primary key dept_id is safer. In standard SQL, any selected non-aggregate column must appear in GROUP BY. PostgreSQL relaxes this when you group by the primary key.
-- Safe: group by primary key
GROUP BY d.dept_id, d.dept_name
-- dept_name is functionally dependent on dept_id
How do you filter to departments above a threshold?
Use HAVING to filter groups after aggregation. WHERE filters rows before grouping; HAVING filters the aggregated result.
-- HAVING: only departments with more than 10 employees
SELECT d.dept_name, COUNT(e.emp_id) AS employee_count
FROM department d
LEFT JOIN employees e ON e.dept_id = d.dept_id
GROUP BY d.dept_id, d.dept_name
HAVING COUNT(e.emp_id) > 10
ORDER BY employee_count DESC;
Expected output:
dept_name | employee_count
-------------+---------------
Loans | 25
Operations | 18
Fraud | 12
-- Crypto Desk (0) excluded by HAVING
How do you count distinct values per group?
Use COUNT(DISTINCT column) to count unique values. For example, count distinct managers per department.
-- COUNT DISTINCT: unique managers per department
SELECT d.dept_name, COUNT(DISTINCT e.manager_id) AS unique_managers
FROM department d
LEFT JOIN employees e ON e.dept_id = d.dept_id
GROUP BY d.dept_id, d.dept_name;
How do you implement this in Java?
package com.bank.service;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedHashMap;
import java.util.Map;
public class HRReportService {
public Map<String, Integer> getHeadcountPerDepartment(
Connection conn) throws SQLException {
String sql = """
SELECT d.dept_name, COUNT(e.emp_id) AS employee_count
FROM department d
LEFT JOIN employees e ON e.dept_id = d.dept_id
GROUP BY d.dept_id, d.dept_name
ORDER BY employee_count DESC
""";
Map<String, Integer> result = new LinkedHashMap<>();
try (PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
result.put(rs.getString("dept_name"), rs.getInt("employee_count"));
}
}
return result;
}
}
What about performance on large tables?
An index on employees(dept_id) makes the join and grouping efficient. Without it, the database must scan the entire employees table.
-- Create index for efficient join and grouping
CREATE INDEX idx_emp_dept ON employees(dept_id);
What should a senior engineer know?
A senior engineer knows to use LEFT JOIN with COUNT(e.emp_id) so empty departments show zero, to group by the primary key for safety, to use HAVING for group-level filters, and to index the foreign key column for performance on large tables.
Visual Overview
┌─────────────────────────────────────────────────────┐ │ Count Employees Per Department │ ├─────────────────────────────────────────────────────┤ │ │ │ department employees │ │ ┌──────────────┐ ┌──────────────┐ │ │ │ Loans │──match─│ Rajesh │ │ │ │ │──match─│ Priya │ │ │ │ │──match─│ ... (25) │ │ │ │ Fraud │──match─│ ... (12) │ │ │ │ Crypto Desk │──×────→│ (NULL) │ ← no emp│ │ └──────────────┘ └──────────────┘ │ │ │ │ GROUP BY dept_name: │ │ ┌──────────────┬────────────────────────┐ │ │ │ Department │ COUNT(e.emp_id) │ │ │ ├──────────────┼────────────────────────┤ │ │ │ Loans │ 25 │ │ │ │ Fraud │ 12 │ │ │ │ Crypto Desk │ 0 (NULL not counted) │ │ │ └──────────────┴────────────────────────┘ │ │ │ │ If COUNT(*) instead: Crypto Desk = 1 (WRONG!) │ │ │ └─────────────────────────────────────────────────────┘
Key Takeaway
To count employees per department, join the employees table to the department table on the department ID, group by the department, and count. The important decision is whether empty departments must appear. With an INNER JOIN and COUNT(*), only departments with staff are shown. With a LEFT JOIN from department to employees and COUNT(e.emp_id), every department appears and empty ones correctly show zero.
The reason COUNT(e.emp_id) matters is that a LEFT JOIN manufactures a NULL-filled row for an empty department, and COUNT(*) would report one while COUNT(column) ignores NULLs and correctly reports zero. This is the classic LEFT JOIN COUNT trap.
In Priya's HR system at National Bank of India, a newly created Crypto Desk with no staff was disappearing from the headcount report because it used an INNER JOIN. Rajesh fixed it with a LEFT JOIN and COUNT(emp_id) so the empty department shows zero. The senior points he added are grouping by the department primary key for safety, indexing the employees' department column for performance, and using HAVING for group-level filters.
Remember one sentence: JOIN employees to departments, GROUP BY department, and COUNT the employee key with a LEFT JOIN so empty departments show zero.
---