How do you use the OVER() clause to calculate running totals?
JackBrn
Posted by JackBrn
Last Updated: July 23, 2024
SQL Running
The OVER() clause is a powerful feature in SQL that allows you to perform calculations across a set of rows related to the current row without collapsing the results into a single output row, as would happen with aggregate functions. To calculate running totals using the OVER() clause, you generally use the SUM() function along with PARTITION BY and ORDER BY to define the order of the rows. Here’s a general syntax for calculating running totals: 
SELECT 
    column1,
    column2,
    SUM(column_to_sum) OVER (ORDER BY column_to_order) AS running_total
FROM 
    your_table
ORDER BY 
    column_to_order;
Example
Imagine you have a table named sales with the following structure: | sale_date | amount | |-----------|--------| | 2023-01-01| 100 | | 2023-01-02| 150 | | 2023-01-03| 200 | You want to calculate a running total of amount ordered by sale_date. Here's how you would write the SQL query: 
SELECT 
    sale_date,
    amount,
    SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM 
    sales
ORDER BY 
    sale_date;
Explanation:
- SUM(amount): This is the aggregate function that will calculate the sum of the amount column. - OVER (ORDER BY sale_date): This specifies that the sum should be calculated in an ordered manner based on sale_date. This means for each row, SQL will look at the current row and all preceding rows in the order defined. - AS running_total: This gives a name to the calculated running total for easier reference.
Result
The output of this query would be: | sale_date | amount | running_total | |-----------|--------|---------------| | 2023-01-01| 100 | 100 | | 2023-01-02| 150 | 250 | | 2023-01-03| 200 | 450 |
Including PARTITION BY
If you want to calculate running totals for different categories within the same dataset, you can use the PARTITION BY clause. For example, if there is a category column in the sales table, you could do: 
SELECT 
    sale_date,
    category,
    amount,
    SUM(amount) OVER (PARTITION BY category ORDER BY sale_date) AS running_total
FROM 
    sales
ORDER BY 
    category, sale_date;
This would calculate separate running totals for each category while maintaining the order by sale_date.
Default Image