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
GROUP BY date
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. We send one update every week. Join 400+ data analysts who are leveling up with our recipes. 👊

No spam, ever! Unsubscribe any time.