Running total of salaries by department.

Running Total of Salaries by Department in SQL: A Complete Guide

How to build cumulative totals with window functions, avoid the common pitfalls, and keep the query fast

Introduction

A cumulative number shows up in almost every reporting request: sales to date, headcount growth, or, in this guide, the running total of salaries within each department. A running total shows how a value accumulates row by row, and it resets whenever the group changes.

Before window functions, this meant a self-join or a correlated subquery, both slow and hard to read. Today a single SUM() OVER (...) does the job. This post walks through the full solution, explains each clause, covers the mistakes that trip people up, and finishes with performance tuning.

The Scenario

We have an Employees table and want to see, for every employee, how much the department's payroll has accumulated as of their hire date.

CREATE TABLE Employees (
    EmployeeId INT PRIMARY KEY,
    Name       VARCHAR(100),
    Department VARCHAR(50),
    Salary     DECIMAL(10,2),
    HireDate   DATE
);

Sample data:

INSERT INTO Employees (EmployeeId, Name, Department, Salary, HireDate) VALUES
(1, 'Asha',  'Engineering', 90000, '2020-01-10'),
(2, 'Ravi',  'Engineering', 85000, '2021-03-05'),
(3, 'Meera', 'Engineering', 95000, '2022-07-19'),
(4, 'Karan', 'Sales',       60000, '2019-11-02'),
(5, 'Neha',  'Sales',       65000, '2021-06-14');

The Solution

SELECT
    Department,
    EmployeeId,
    Name,
    HireDate,
    Salary,
    SUM(Salary) OVER (
        PARTITION BY Department
        ORDER BY HireDate, EmployeeId
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS RunningTotal
FROM Employees
ORDER BY Department, HireDate, EmployeeId;

Result:

Department	Name	HireDate	Salary	RunningTotal
Engineering	Asha	2020-01-10	90,000	90,000
Engineering	Ravi	2021-03-05	85,000	175,000
Engineering	Meera	2022-07-19	95,000	270,000
Sales	Karan	2019-11-02	60,000	60,000
Sales	Neha	2021-06-14	65,000	125,000

Notice that Sales starts again at 60,000 rather than continuing from Engineering's total. That reset is the point of partitioning.

How the Query Works

The OVER clause has three parts, and each one controls a different aspect of the calculation.

1. PARTITION BY Department

This splits the rows into independent groups, one per department. The sum is calculated inside each group, so totals never leak from one department into another. Unlike GROUP BY, partitioning does not collapse rows. Every employee row stays in the output.

2. ORDER BY HireDate, EmployeeId

A running total only means something with an order. Here we accumulate in hire-date order. EmployeeId is added as a tie-breaker: if two employees share a hire date, the database would otherwise be free to order them arbitrarily, and the running totals could change between executions.

3. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

This is the frame. It tells the database which rows, relative to the current one, feed the sum. Here it means "from the first row of the partition through the current row." That is exactly the definition of a running total.

The Common Gotcha: Always Specify ROWS

You can write the query without the frame:

SUM(Salary) OVER (PARTITION BY Department ORDER BY HireDate)

It looks equivalent, but it isn't. Without a frame clause, SQL uses RANGE UNBOUNDED PRECEDING by default. RANGE treats rows with equal ORDER BY values as peers, so if two employees were hired on the same day, both rows receive the same total, which is usually not the intent. In SQL Server, RANGE is also typically slower because it can spill to disk.

Writing ROWS explicitly fixes both problems and makes your intent obvious to the next reader.

Variation 1: Monthly Department Payroll

Often you want the cumulative payroll over time rather than per employee. Aggregate first with GROUP BY, then apply the window function on top of the result:

WITH Monthly AS (
    SELECT Department,
           DATEFROMPARTS(YEAR(HireDate), MONTH(HireDate), 1) AS Month,
           SUM(Salary) AS MonthlySalary
    FROM Employees
    GROUP BY Department, DATEFROMPARTS(YEAR(HireDate), MONTH(HireDate), 1)
)
SELECT Department, Month, MonthlySalary,
       SUM(MonthlySalary) OVER (
           PARTITION BY Department
           ORDER BY Month
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS RunningTotal
FROM Monthly
ORDER BY Department, Month;

The CTE (WITH Monthly AS ...) produces one row per department per month. The outer query then accumulates those monthly figures. This two-step pattern, aggregate then window, is useful any time you need a running total over time periods.

Variation 2: Running Total Beside the Department Total

You can use several windows in the same query. This version shows the running total and the department's overall total on every row:

SELECT Department, Name, Salary,
       SUM(Salary) OVER (
           PARTITION BY Department
           ORDER BY Salary DESC, EmployeeId
           ROWS UNBOUNDED PRECEDING
       ) AS RunningTotal,
       SUM(Salary) OVER (PARTITION BY Department) AS DepartmentTotal
FROM Employees;

The second SUM has no ORDER BY, so it covers the whole partition and returns the same total on every row of the department. From here, the share of the department payroll reached so far is simply:

RunningTotal * 100.0 / DepartmentTotal

Ordering by Salary DESC makes this a "top earners first" view, which is handy for seeing how much of the payroll the highest-paid people account for.

Variation 3: Without Window Functions

On very old databases that lack window functions, a correlated subquery gives the same result:

SELECT e.Department, e.Name, e.Salary,
       (SELECT SUM(e2.Salary)
        FROM Employees e2
        WHERE e2.Department = e.Department
          AND (e2.HireDate < e.HireDate
               OR (e2.HireDate = e.HireDate AND e2.EmployeeId <= e.EmployeeId))
       ) AS RunningTotal
FROM Employees e;

It works, but it re-scans the table for every output row, so cost grows quickly with data size. The window version reads the data once, which is why it is the preferred approach wherever it's available.

Performance Tuning

An index that matches the PARTITION BY and ORDER BY columns lets the database read rows already in the right order and skip a separate sort:

CREATE INDEX IX_Employees_Dept_Hire
ON Employees (Department, HireDate, EmployeeId)
INCLUDE (Salary);

The key columns follow the partition and sort order, and INCLUDE (Salary) makes the index covering, so the query never has to visit the base table.

Other tips:

• Filter early. Apply WHERE conditions before the window is computed so fewer rows are processed.
• Read the execution plan. A Sort or Spool operator usually means the index doesn't match the window's partition and order columns.
• Select only what you need. Wide result sets make the window operator more expensive.

Database Compatibility

This syntax works in:

Database	Support
Azure SQL Database / SQL Server	2012 and later
PostgreSQL	8.4 and later
MySQL	8.0 and later
Oracle	Yes
SQLite	3.25 and later

The only dialect-specific part of the examples is DATEFROMPARTS. In PostgreSQL, replace it with DATE_TRUNC('month', HireDate).

Key Takeaways

1. Use SUM() OVER (PARTITION BY ... ORDER BY ...) for running totals instead of self-joins or subqueries.
2. PARTITION BY resets the total per group, and unlike GROUP BY it keeps every row.
3. Add a unique tie-breaker column to the ORDER BY for deterministic results.
4. Always specify ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to avoid tie-related surprises and the slower RANGE default.
5. To get cumulative values over time, aggregate first in a CTE, then apply the window.
6. Back the query with an index on the partition and order columns, including the summed column.