Running Totals

Displaying a running total in SQL Server is very straightforward, thanks to window functions and the OVER clause.
We’ll set up an example with some records for a payment amount over several days.

drop table if exists #Test;

create table #Test (
PaymentDate date not null primary key,
Amount int not null
);

insert into #Test(PaymentDate, Amount) 
values ('2018-05-29', 25), ('2018-05-30',10), ('2018-05-31',20), 
	('2018-06-01',30), ('2018-06-02',40), ('2018-06-03', 15);

select * from #Test;

Adding a running total is just a matter of SUM with an OVER clause.

select PaymentDate, Amount,
	sum(Amount) over (order by PaymentDate) as RunningTotal
from #Test
order by PaymentDate;

The RunningTotal column will show the total of all payments made on that day and any earlier date.

We can also use PARTITION to display a running total for just that month as well.

select PaymentDate, Amount, datename(month, PaymentDate) as MonthName,
	sum(Amount) over (partition by month(PaymentDate) order by PaymentDate) as MonthRunningTotal
from #Test
order by PaymentDate;

In the first example we used default settings for the OVER clause, which is for the current row plus all rows before it. Here is the first query with the default explicitly given.

select PaymentDate, Amount,
	sum(Amount) over (order by PaymentDate rows unbounded preceding) as RunningTotal
from #Test
order by PaymentDate;

We can change this setting to return other combinations. In the final example, we’ll return the running total as the payment amount for the current row plus the amount from the day before.


select PaymentDate, Amount,
	sum(Amount) over (order by PaymentDate rows 1 preceding) as RunningTotal
from #Test
order by PaymentDate;

Window Functions
SELECT – OVER Clause

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: