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.