Calculating Running Total in SQL

While it's easy enough to compute the total of selected rows, it's far more interesting to compute a running total over time. A running total is the summation of a sequence of numbers which is updated each time a number is added to the sequence, by adding the value of the new number to the previous running total.

For example, if your dataset is a "users_joined" table like so:

date user_id
2016-01-01 1
2016-01-02 2
2016-01-02 3
2016-01-02 4
2016-01-02 5
2016-01-02 6
2016-01-02 7
2016-01-03 8
... ...

You can compute the total number of users joined in a day like this:

SELECT date, COUNT(user_id) FROM users_joined 
GROUP BY date ORDER BY date;

Which will give you a result like this:

date count
2016-01-01 1
2016-01-02 6
2016-01-03 1
... ...

User Counts

If you wanted to compute a running total (or cumulative sum) of the counts, you can use a SQL window function to achieve that:

SELECT date,
       COUNT(user_id) as count,
       SUM(COUNT(user_id)) OVER (ORDER BY date) as running_total
FROM users_joined
ORDER BY date;
date count running_total
2016-01-01 1 1
2016-01-02 6 7
2016-01-03 1 8
... ... ...

Running Total User Counts

No fuss, just SQL We are open sourcing everything from the experience working with our agency clients. They spend thousands of dollars to get this level of detailed analysis – which you can now get for free. You'll learn bucketing, finding outliers, calculating correlations, budgeting & forecasting, return on spend and investment, data smoothing and cleaning applied to freemium, transactional, advertising, two-sided marketplace and subscription based business models. We send one update every week. Join 400+ data analysts who are leveling up with our recipes. 👊

No spam, ever! Unsubscribe any time.