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. See past emails here.

results matching ""

    No results matching ""