Do you need to generate a running total in your SQL query? You've come to the right place.
In this video, you'll learn what a running total is, how to generate one in SQL, and some examples.
This will work on many databases such as Oracle, SQL Server, MySQL, and PostgreSQL.
Let's get started.
A running total, or cumulative sum, is a value
that indicates the total of a value in the current row and all rows above it.
You could use it to see the total value of orders placed so far for the month. Or you
could use it on a sports website to display the team's score so far for every score that is made.
There are many uses for it. Let's see how it can be done.
We'll use some sample data to demonstrate this concept.
Here's our sample table, called sales_history.
It represents orders and the value of orders that have been made over time.
You can find the SQL scripts to create and populate this table in the description below.
If we want to select from the table, our query would look like this.
We simply select the columns from the sales history table.
Let's say we want to see a running total of the order value,
which would show the order value plus all of the previous order values for each row.
So, how do we display a running total?
We use something called a "window function".
A window function is a way to write a function so that it looks over a range of records,
or a window, instead of all records or a single record.
In our example, we can use the SUM function to get the total of the order value column.
We can write the SUM function as a window function to show the SUM of
all order values of the current record and all previous records.
The SUM function, written as a window function, looks like this.
This has a few components. The expression is the column we want to sum,
which in this case is order value.
The partition clause specifies the range of data to use. The order by
clause specifies how data is ordered in the range.
Let's see how we can use this in our query.
Here's our Select query from earlier.
Next, we add in the SUM function.
Inside the SUM function, we add in the order_value column.
Then, after the brackets, we add the word OVER and some more brackets.
Inside the brackets for the OVER clause, we can add two things:
the partition clause, which is the range of data to use,
and the order by clause, which is how the data is ordered for the calculation.
For our running total, we want to calculate the SUM based on all records so far.
So, we don't need to add the partition clause.
We want to include the order by clause though, as that's used for the running total.
A running total includes all of the values before it. But how do we know what comes before it?
We can use the order ID value.
So, the running total for order ID 2 includes the order value for orders 1 and 2.
For order 3, it would include orders 1, 2, and 3.
For our query, we specify this order by clause inside the over clause.
Next, we can give this column a column alias.
We don't need to, but it will make our output a lot neater. We do this by adding AS running_total.
Finally, we should order the entire result set. If we don't order the results,
then the results may appear in a different order than the running total and it may seem confusing.
The order by inside the Over clause is only for the sum function, not for ordering the results.
So, let's add the order by clause to the whole query. We order by order_id in ascending order.
If we run this query, we can see the results here.
We can see a new column called running_total. It shows our running total for the order value.
The first row has a running total of 15 as it only includes one row.
The second value is 23, which includes the 15 from the first row and the 8 from the second row.
The running total increases as we go down the result set.
So, in summary, here's what the SUM function looks like to generate a running total.
We have a column to sum inside the SUM function,
then inside the over clause, we have the column that determines the order.
The example we just saw shows a running total for all records.
What if you want to see a running total in different groups?
For example, the running total of sales per month, or scores in a match by each player.
You can do this with a slight modification of the SUM function.
Here's our query from earlier, which includes the SUM function with an OVER clause.
Inside the OVER clause, there is another keyword that can be added,
which is the partition by clause. We ignored this in the earlier example, but it can be useful now.
This partition by clause allows us to specify groups when calculating this SUM value.
For our sample data, it means we can calculate the running total of orders for each sales person.
To do this, we add in partition by salesperson into the OVER clause.
Everything else remains the same.
If we run our query, this is what we see. We can see the same running total column,
but the values are different. The running total is calculated for each salesperson separately.
For example, the first 3 rows have different running totals,
as they are the first values for each salesperson. Row 4 is for John, who had an order value of 15
before and this order value is 2, so the running total is 17. We can see that John's running total
is also shown on the last row and shows 21, because the order value of 4 is added to it.
Each running total value is the sum of all order values before it
for each specific salesperson value.
So, that's how you can generate a running total using SQL.
You use the SUM function with the OVER clause, and can add in the Partition
By clause if you want the running total to be calculated for groups.
Don't forget to add a final Order By clause if you want to see the data in an order that makes sense.
And that brings us to the end of this tutorial video on generating a running total in SQL.
If you learned something new from this video, make sure to subscribe to my channel.
If you want to learn more about database design and development,
visit databasestar.com. That’s where I share my best database-related content.
Which tip from this video was the most helpful? Was it the fact that SUM can be used as a window
function, or that the partition by clause is used to specify groups, or something else?
Thanks for watching.