sql / intermediate
Snippet
Calculating Running Totals with the ANSI SQL OVER Clause
Window functions perform calculations across a set of table rows related to the current row without collapsing the result into a single group. By using SUM(...) OVER with PARTITION BY and ORDER BY, SQL accumulates values dynamically row by row for each partition.
snippet.sql
sql
1
2
3
4
5
6
7
8
9
10
SELECTorder_id,customer_id,order_date,order_amount,SUM(order_amount) OVER (PARTITION BY customer_idORDER BY order_date) AS running_totalFROM orders;
Breakdown
1
SELECT
Starts the projection list specifying columns and window calculation.
2
SUM(order_amount) OVER (
Initiates the window aggregate function without collapsing rows into a GROUP BY.
3
PARTITION BY customer_id
Resets the cumulative calculation boundary for each individual customer.
4
ORDER BY order_date
Sorts records chronologically within each partition to calculate the running total.
5
) AS running_total
Closes the window clause and assigns an alias to the calculated cumulative column.