Get a running sum in SQL

Running sums are useful in situations where you want to get a total, but you also want to see how much each entry of a list contributes to the total. So each entry is accompanied by a partial sum, up to that point of the list. While getting a total in SQL is trivial, getting a running sum requires the use of window functions.

Problem: Show the sum of 2020 income, including a month by month running sum.

SELECT
    month,
    SUM (income) OVER (ORDER BY month)
        AS running_sum
    FROM sales
    WHERE year = 2020;

We’re summing the income column (SUM(income)) ordered by month (OVER (ORDER BY month)) and we restrict this to the year 2020 (WHERE year = 2020).

We could invert the order of the running sum as well, with ORDER BY month DESC.

This is a trivial running sum case. There are more complex cases, but we’re leaving them to future articles.

Reference

Other articles: