How to sort Employee data, based on department count

Sorting Employee Data by Department Count in SQL Server

Learn how to efficiently sort employee records based on the number of employees in their respective departments using SQL Server, covering various SQL techniques.

When working with employee data in SQL Server, a common requirement is to organize records not just by employee attributes, but also by characteristics of their associated departments. One such scenario is sorting employees based on the total count of employees within their department. This article will guide you through different SQL techniques to achieve this, from basic joins to more advanced window functions, ensuring optimal performance and readability.

Understanding the Data Structure

Before diving into the queries, let's establish a common understanding of our sample data. We'll assume two tables: Employees and Departments. The Employees table contains employee details, including a DepartmentID to link to the Departments table. The Departments table holds department-specific information.

erDiagram
    EMPLOYEES ||--o{ DEPARTMENTS : "has a"
    EMPLOYEES {
        int EmployeeID PK
        varchar EmployeeName
        int DepartmentID FK
        decimal Salary
    }
    DEPARTMENTS {
        int DepartmentID PK
        varchar DepartmentName
    }

Entity-Relationship Diagram for Employees and Departments

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(100)
);

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(100),
    DepartmentID INT,
    Salary DECIMAL(10, 2),
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

INSERT INTO Departments (DepartmentID, DepartmentName) VALUES
(1, 'HR'),
(2, 'Engineering'),
(3, 'Sales'),
(4, 'Marketing');

INSERT INTO Employees (EmployeeID, EmployeeName, DepartmentID, Salary) VALUES
(101, 'Alice Smith', 1, 60000.00),
(102, 'Bob Johnson', 2, 85000.00),
(103, 'Charlie Brown', 1, 62000.00),
(104, 'Diana Prince', 3, 75000.00),
(105, 'Eve Adams', 2, 90000.00),
(106, 'Frank White', 2, 88000.00),
(107, 'Grace Lee', 4, 70000.00),
(108, 'Henry King', 3, 78000.00),
(109, 'Ivy Green', 2, 92000.00),
(110, 'Jack Black', 1, 58000.00);

SQL script to create and populate sample Departments and Employees tables.

Method 1: Using a Subquery in the ORDER BY Clause

A straightforward approach is to calculate the department employee count using a subquery and then use that count for sorting. This method is intuitive but can sometimes be less performant for very large datasets, as the subquery might be re-evaluated for each row during the sort operation, depending on the optimizer.

SELECT
    e.EmployeeName,
    d.DepartmentName,
    e.Salary
FROM
    Employees e
JOIN
    Departments d ON e.DepartmentID = d.DepartmentID
ORDER BY
    (SELECT COUNT(*) FROM Employees WHERE DepartmentID = e.DepartmentID) DESC,
    e.EmployeeName;

Sorting employees by department count using a subquery in ORDER BY.

Method 2: Using a Common Table Expression (CTE) or Derived Table

A more efficient and often more readable way is to pre-calculate the department counts using a Common Table Expression (CTE) or a derived table. This allows you to join the pre-calculated counts with the employee data before sorting, avoiding repeated calculations.

WITH DepartmentCounts AS (
    SELECT
        DepartmentID,
        COUNT(*) AS EmployeeCount
    FROM
        Employees
    GROUP BY
        DepartmentID
)
SELECT
    e.EmployeeName,
    d.DepartmentName,
    e.Salary,
    dc.EmployeeCount
FROM
    Employees e
JOIN
    Departments d ON e.DepartmentID = d.DepartmentID
JOIN
    DepartmentCounts dc ON e.DepartmentID = dc.DepartmentID
ORDER BY
    dc.EmployeeCount DESC,
    e.EmployeeName;

Sorting employees by department count using a CTE.

Method 3: Using Window Functions (COUNT() OVER)

Window functions provide an elegant and often highly performant solution for this type of problem. The COUNT(*) OVER (PARTITION BY DepartmentID) function calculates the count of employees for each department without needing a separate GROUP BY clause or a join to a derived table. This is typically the most efficient method for large datasets.

SELECT
    e.EmployeeName,
    d.DepartmentName,
    e.Salary,
    COUNT(*) OVER (PARTITION BY e.DepartmentID) AS DepartmentEmployeeCount
FROM
    Employees e
JOIN
    Departments d ON e.DepartmentID = d.DepartmentID
ORDER BY
    DepartmentEmployeeCount DESC,
    e.EmployeeName;

Sorting employees by department count using the COUNT() window function.

Choosing the Right Method

The best method depends on your specific needs and the scale of your data. For smaller datasets, all methods might perform similarly. However, for larger tables:

• Window Functions (Method 3) are generally the most efficient and recommended approach due to their ability to calculate aggregates without separate grouping and joining steps.
• CTE/Derived Tables (Method 2) offer good performance and improved readability compared to subqueries, especially when the count calculation is complex or reused.
• Subquery in ORDER BY (Method 1) is simple to write but can be the least performant for large tables if the optimizer doesn't handle it efficiently.