Every aggregate function (SUM, AVG, COUNT, MIN, MAX) can be used as a window function by adding OVER (...). Instead of collapsing rows, it adds the aggregate next to each row.
query.sql
SQL
CREATE TABLE sales (
id INT AUTO_INCREMENT PRIMARY KEY,
region VARCHAR(10) NOT NULL,
sold_on DATE NOT NULL,
amount DECIMAL(8,2) NOT NULL
);
INSERT INTO sales (region, sold_on, amount) VALUES
('north', '2026-06-01', 100), ('north', '2026-06-02', 250), ('north', '2026-06-04', 50),
('south', '2026-06-01', 300), ('south', '2026-06-03', 120),
('west', '2026-06-02', 500);Without ORDER BY: one value for the whole window
query.sql
SQL
SELECT id, region, amount,
SUM(amount) OVER () AS grand_total,
SUM(amount) OVER (PARTITION BY region) AS region_total,
ROUND(100 * amount / SUM(amount) OVER (), 1) AS percent_of_all
FROM sales
ORDER BY id;
Output
OUTPUT
+----+--------+--------+-------------+--------------+----------------+
| id | region | amount | grand_total | region_total | percent_of_all |
+----+--------+--------+-------------+--------------+----------------+
| 1 | north | 100.00 | 1320.00 | 400.00 | 7.6 |
| 2 | north | 250.00 | 1320.00 | 400.00 | 18.9 |
| 3 | north | 50.00 | 1320.00 | 400.00 | 3.8 |
| 4 | south | 300.00 | 1320.00 | 420.00 | 22.7 |
| 5 | south | 120.00 | 1320.00 | 420.00 | 9.1 |
| 6 | west | 500.00 | 1320.00 | 500.00 | 37.9 |
+----+--------+--------+-------------+--------------+----------------+
6 rows in setWithout ORDER BY inside OVER, the aggregate covers the whole partition, and every row in the partition shows the same total. This gives percent-of-total, with no subquery.
With ORDER BY: a running total
query.sql
SQL
SELECT id, region, sold_on, amount,
SUM(amount) OVER (ORDER BY sold_on, id) AS running_total
FROM sales
ORDER BY sold_on, id;
Output
OUTPUT
+----+--------+------------+--------+---------------+
| id | region | sold_on | amount | running_total |
+----+--------+------------+--------+---------------+
| 1 | north | 2026-06-01 | 100.00 | 100.00 |
| 4 | south | 2026-06-01 | 300.00 | 400.00 |
| 2 | north | 2026-06-02 | 250.00 | 650.00 |
| 6 | west | 2026-06-02 | 500.00 | 1150.00 |
| 5 | south | 2026-06-03 | 120.00 | 1270.00 |
| 3 | north | 2026-06-04 | 50.00 | 1320.00 |
+----+--------+------------+--------+---------------+
6 rows in setWith ORDER BY, the default frame is “from the first row to the current row”, so SUM becomes a running total.
A running total per group
query.sql
SQL
SELECT region, sold_on, amount,
SUM(amount) OVER (PARTITION BY region ORDER BY sold_on, id) AS region_running_total,
COUNT(*) OVER (PARTITION BY region ORDER BY sold_on, id) AS sale_number_in_region
FROM sales
ORDER BY region, sold_on;
Output
OUTPUT
+--------+------------+--------+----------------------+-----------------------+
| region | sold_on | amount | region_running_total | sale_number_in_region |
+--------+------------+--------+----------------------+-----------------------+
| north | 2026-06-01 | 100.00 | 100.00 | 1 |
| north | 2026-06-02 | 250.00 | 350.00 | 2 |
| north | 2026-06-04 | 50.00 | 400.00 | 3 |
| south | 2026-06-01 | 300.00 | 300.00 | 1 |
| south | 2026-06-03 | 120.00 | 420.00 | 2 |
| west | 2026-06-02 | 500.00 | 500.00 | 1 |
+--------+------------+--------+----------------------+-----------------------+
6 rows in setThe running total restarts for each region.
Running average, minimum and maximum
query.sql
SQL
SELECT sold_on, amount,
ROUND(AVG(amount) OVER (ORDER BY sold_on, id), 2) AS running_average,
MAX(amount) OVER (ORDER BY sold_on, id) AS highest_so_far,
MIN(amount) OVER (ORDER BY sold_on, id) AS lowest_so_far
FROM sales
ORDER BY sold_on, id;
Output
OUTPUT
+------------+--------+-----------------+----------------+---------------+
| sold_on | amount | running_average | highest_so_far | lowest_so_far |
+------------+--------+-----------------+----------------+---------------+
| 2026-06-01 | 100.00 | 100.00 | 100.00 | 100.00 |
| 2026-06-01 | 300.00 | 200.00 | 300.00 | 100.00 |
| 2026-06-02 | 250.00 | 216.67 | 300.00 | 100.00 |
| 2026-06-02 | 500.00 | 287.50 | 500.00 | 100.00 |
| 2026-06-03 | 120.00 | 254.00 | 500.00 | 100.00 |
| 2026-06-04 | 50.00 | 220.00 | 500.00 | 50.00 |
+------------+--------+-----------------+----------------+---------------+
6 rows in setTies and RANGE (a subtle point)
The default frame, when ORDER BY is given, is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. With RANGE, rows that tie on the sort value are treated as one group and all show the same running total:
query.sql
SQL
SELECT sold_on, amount, SUM(amount) OVER (ORDER BY sold_on) AS running_with_range
FROM sales
ORDER BY sold_on, id;
Output
OUTPUT
+------------+--------+--------------------+
| sold_on | amount | running_with_range |
+------------+--------+--------------------+
| 2026-06-01 | 100.00 | 400.00 |
| 2026-06-01 | 300.00 | 400.00 |
| 2026-06-02 | 250.00 | 1150.00 |
| 2026-06-02 | 500.00 | 1150.00 |
| 2026-06-03 | 120.00 | 1270.00 |
| 2026-06-04 | 50.00 | 1320.00 |
+------------+--------+--------------------+
6 rows in setOn 2026-06-01 there are two sales (north 100 and south 300). Both rows show 400, because tied rows share the same frame end. If you want a strict row-by-row running total, either add a unique tie-breaker to the ORDER BY (ORDER BY sold_on, id) or use ROWS:
query.sql
SQL
SELECT sold_on, amount, SUM(amount) OVER (ORDER BY sold_on, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_with_rows
FROM sales
ORDER BY sold_on, id;
Output
OUTPUT
+------------+--------+-------------------+
| sold_on | amount | running_with_rows |
+------------+--------+-------------------+
| 2026-06-01 | 100.00 | 100.00 |
| 2026-06-01 | 300.00 | 400.00 |
| 2026-06-02 | 250.00 | 650.00 |
| 2026-06-02 | 500.00 | 1150.00 |
| 2026-06-03 | 120.00 | 1270.00 |
| 2026-06-04 | 50.00 | 1320.00 |
+------------+--------+-------------------+
6 rows in setRunning balance
A bank-style balance needs deposits and withdrawals as signed amounts:
query.sql
SQL
CREATE TABLE transactions (id INT AUTO_INCREMENT PRIMARY KEY, happened_on DATE, description VARCHAR(20), amount DECIMAL(8,2));
INSERT INTO transactions (happened_on, description, amount) VALUES
('2026-07-01', 'Salary', 5000), ('2026-07-03', 'Rent', -1500), ('2026-07-05', 'Groceries', -220.50), ('2026-07-09', 'Refund', 80);
SELECT happened_on, description, amount, SUM(amount) OVER (ORDER BY happened_on, id) AS balance
FROM transactions
ORDER BY happened_on, id;
Output
OUTPUT
+-------------+-------------+----------+---------+
| happened_on | description | amount | balance |
+-------------+-------------+----------+---------+
| 2026-07-01 | Salary | 5000.00 | 5000.00 |
| 2026-07-03 | Rent | -1500.00 | 3500.00 |
| 2026-07-05 | Groceries | -220.50 | 3279.50 |
| 2026-07-09 | Refund | 80.00 | 3359.50 |
+-------------+-------------+----------+---------+
4 rows in setShare within a group
query.sql
SQL
SELECT region, sold_on, amount, ROUND(100 * amount / SUM(amount) OVER (PARTITION BY region), 1) AS percent_of_region
FROM sales
ORDER BY region, sold_on;
Output
OUTPUT
+--------+------------+--------+-------------------+
| region | sold_on | amount | percent_of_region |
+--------+------------+--------+-------------------+
| north | 2026-06-01 | 100.00 | 25.0 |
| north | 2026-06-02 | 250.00 | 62.5 |
| north | 2026-06-04 | 50.00 | 12.5 |
| south | 2026-06-01 | 300.00 | 71.4 |
| south | 2026-06-03 | 120.00 | 28.6 |
| west | 2026-06-02 | 500.00 | 100.0 |
+--------+------------+--------+-------------------+
6 rows in setWindow aggregate vs GROUP BY
GROUP BY | Window aggregate | |
|---|---|---|
| Result rows | One per group | All rows kept |
| Detail and total together | Needs a join or a subquery | Same row |
| Use for | Reports of totals | Running totals, percent of total, comparing a row with its group |
You can combine them: group first, then use a window over the grouped result (Window Functions).
lightbulb
Always add a tie-breaker
For a running total, end the
ORDER BY with a unique column (id). Otherwise tied rows show the same total, and the order inside the ties is arbitrary.