Window Functions Aggregates SUM OVER · running total · partition total

SUM Window Function

SUM with an OVER() clause keeps every row and tags it with a running or partition total. The everyday tool for cumulative balances, share-of-total, and per-group totals shown row-by-row.

SUM as a window function

SUM adds numbers together. As a window function — with an OVER() clause — it adds them across the rows of each window, returning one running or aggregate total per row instead of collapsing the rows.

Two everyday uses: the running total (with ORDER BY) and the partition total (without). Both are awkward to express with regular GROUP BY without losing per-row detail.

Syntax

SUM(expression) OVER (
    [PARTITION BY column_list]
    [ORDER BY column_list]
    [frame_clause]
)

Demo dataset

SQL — Create & insert

CREATE TABLE employees (
    employee_id    INT,
    first_name     VARCHAR(50),
    last_name      VARCHAR(50),
    salary         DECIMAL(10, 2),
    manager_id     INT,
    department_id  INT
);

INSERT INTO employees VALUES
(200, 'Jennifer',  'Whalen',     4400.00, 101, 1),
(201, 'Michael',   'Hartstein', 13000.00, 100, 2),
(202, 'Pat',       'Fay',        6000.00, 201, 2),
(203, 'Susan',     'Mavris',     6500.00, 101, 4),
(204, 'Hermann',   'Baer',      10000.00, 101, 4),
(205, 'Shelley',   'Higgins',   12008.00, 101, 1),
(206, 'William',   'Gietz',      8300.00, 205, 1),
(100, 'Steven',    'King',      24000.00, NULL, 9),
(101, 'Neena',     'Kochhar',   17000.00, 100, 9),
(102, 'Lex',       'De Haan',   17000.00, 100, 9);

Example 1 — total payroll on every row

SQL

SELECT first_name,
       salary,
       SUM(salary) OVER () AS total_payroll
FROM   employees;

Empty OVER() means "the whole result set." The same total appears alongside every row.

Example 2 — sum per department

SQL

SELECT first_name,
       department_id,
       salary,
       SUM(salary) OVER (PARTITION BY department_id) AS dept_total,
       ROUND(salary * 100.0
             / SUM(salary) OVER (PARTITION BY department_id), 1) AS pct_of_dept
FROM   employees;

The pct_of_dept column shows what share of their department's payroll each employee represents — a single query, no self-join.

Example 3 — running total

SQL

SELECT employee_id,
       first_name,
       salary,
       SUM(salary) OVER (ORDER BY employee_id) AS running_total
FROM   employees
ORDER  BY employee_id;

With ORDER BY, SUM becomes cumulative — each row's total includes itself plus everything before it. The default frame ("UNBOUNDED PRECEDING to CURRENT ROW") is what produces the running effect.

Example 4 — running total per department

SQL

SELECT department_id,
       employee_id,
       salary,
       SUM(salary) OVER (
           PARTITION BY department_id
           ORDER BY employee_id
       ) AS dept_running_total
FROM   employees
ORDER  BY department_id, employee_id;

Combining PARTITION BY with ORDER BY gives a running total that resets at each department.

Example 5 — trailing window sum

SQL

-- Sum of this row's salary plus the two before it
SELECT employee_id, salary,
       SUM(salary) OVER (
           ORDER BY employee_id
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS sum_last_3
FROM   employees
ORDER  BY employee_id;

Recap

SUM(...) OVER () — overall total tagged onto every row.
SUM(...) OVER (PARTITION BY col) — partition total, resets per group.
SUM(...) OVER (ORDER BY col) — running total.
Combine for running totals that reset per partition.
A frame clause (ROWS BETWEEN N PRECEDING …) gives trailing windows.