running totals with window functions

Topics

• window functions

Sum salaries up to current row, ordered by employee_id:

SELECT name, salary,
       SUM(salary) OVER (
           ORDER BY employee_id
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS running_total
FROM employees;

If we need to reset running total per group, add PARTITION BY clause as well. For example, to compute running totals per department:

SELECT
    department,
    name,
    salary,
    SUM(salary) OVER (
        PARTITION BY department
        ORDER BY employee_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM
    employees;

Related