SQL Running Totals
Let's say you have a table with invoices, containing the invoice amount. Nothing is easier than to put that amount in the result set. But what if you want a running total? With that I mean that the second record shows the sum of record 1 and 2. And the third record shows the sum of record 1, 2 and 3. And so on. Well, since you already have the information in the result set, there is an easy way to do that!
Let the totals run
Just as with Lag and Lead, you can do this (since SQL 2005) using the "over" keyword. Here's an example I wrote for an Exact Globe database. The table and column names might be a bit cryptic but you will easily understand the logic behind it.
sum(tot_bdr) over (order by f.faknr) as RunningTotal
from frhkrg f
inner join cicmpy c on f.debnr = c.debnr
order by InvoiceNumber
Since the order by is on Invoice Number and that one is unique, in each record the running total is the sum from that record plus the previous records.
Here's the result from this query:
Running Totals per customer
Now let's say you want the running totals but not over the total record set but per customer. You can achieve this using the partition by statement. The query would then look like this:
sum(tot_bdr) over (partition by c.cmp_name order by f.faknr) as RunningTotal
from frhkrg f
inner join cicmpy c on f.debnr = c.debnr
order by Name, InvoiceNumber
And the result looks like this:
Using multiple resets
You can specify multiple columns (or better said, values) in the partition by. So you could also do something like reset the running totals per customer and per month. This would be the query for that one:
datepart(Year, fakdat), datepart(Month, fakdat), tot_bdr as Amount,
sum(tot_bdr) over (partition by datepart(Year, fakdat), datepart(Month, fakdat), c.cmp_name order by f.faknr) as RunningTotal
from frhkrg f
inner join cicmpy c on f.debnr = c.debnr
order by Name, InvoiceNumber
Do note that I used both Year and Month. Without the year there would be no distinction between January 2020 and January 2021.
So the running total would sum up everything from January, regardless of the year.
That was not what I wanted, hence the addition of the year.
The resutls for this one look like this:
And it is fast!
Just like with Lag and Lead, the running totals are calculated using the result set.
No sub-queries here. Which means it is almost as fast as without the running totals.
Be sure to use this one if you want to create reports using these values.
Your users will surely appreciate this and they will be amazed by your skills.
Let's keep it our secret how easily this can be done!
Oh, and do let me know when and how you used this one in your projects!
You can leave a comment or send a question to andre@andrespeek.com. Let me know if you got inspired and stay safe and healthy always!