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:
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:
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;
You are reading a series of Advanced SQL tricks.
Read this next: Calculating Running/Moving Average
Join 200+ data analysts who are using our weekly SQL tips to improve their skills. No spam, promise!