Calculating running total in SQL Server

In SQL Server database programming we frequently have requirements to calculate running total. This can be achieved in SQL Server using Sum() with windowing functions. Below I am going thru some of the simplest examples to understand and calculate running total.

Creating Sample data

I am considering small sales data with attributes SalesId, SalesDate, department and SalesAmount. Here we are going to calculate running total on SalesAmount based on SalesDate.

Create Table CustomerSales (SalesId int, Department varchar(15), SalesDate date, SalesAmount int)

Insert into CustomerSales(SalesId, Department, SalesDate, SalesAmount) values
 ( 1, 'DepartMent1', '2016-05-01',50)
,( 2, 'DepartMent1', '2016-05-02',20)
,( 3, 'DepartMent1', '2016-05-03',30)
,( 4, 'DepartMent2', '2016-05-04',150)
,( 5, 'DepartMent2', '2016-05-05',110)
,( 6, 'DepartMent2', '2016-05-06',120)
,( 7, 'DepartMent2', '2016-05-07',10)
,( 8, 'DepartMent2', '2016-05-08',20)
,( 9, 'DepartMent3', '2016-05-09',40)
,(10, 'DepartMent3', '2016-06-10',50)
,(11, 'DepartMent3', '2016-06-11',60)
,(12, 'DepartMent4', '2016-06-12',70)
,(13, 'DepartMent4', '2016-06-13',80)
,(14, 'DepartMent4', '2016-06-14',90)
,(15, 'DepartMent4', '2016-06-15',180)
Running total

Here we are going to use Sum() aggregation along with Windowing function over() by providing appropriate sequencing column as below:

--Running Total
Select *, RunningTotal = sum(SalesAmount) over(order by SalesDate) 
	from dbo.CustomerSales

RunningTotal

Use of order by clause in over() will get the running total.

Percent of Total sales and Running total
We can use above running total logic along with total sales logic to find percent of total sales which will be helpful to understand overall Sales. That can be achieved as below:
--Total Sales
Select *, RunningTotal = sum(SalesAmount) over(order by SalesDate) ,
	TotalSalesAmount = sum(SalesAmount) over() 
	from dbo.CustomerSales

--Percent of Total Sales
Select *, RunningTotal = sum(SalesAmount) over(order by SalesDate) ,
	TotalSalesAmount = sum(SalesAmount) over() ,
	PercentOfTotalSales = Format((sum(SalesAmount) over(order by SalesDate) * 1.0) / Sum(SalesAmount) over(), 'p')
	from dbo.CustomerSales

Here over() clause without order by provides the total salesAmount

TotalSales

To calculate Department wise running total we can use partition by in over() clause as below:

--Running Total for each Department
Select *, RunningTotal = sum(SalesAmount) over(order by SalesDate),
	DepartmentRunningTotal = sum(SalesAmount) over(Partition by Department order by SalesDate) 
	from dbo.CustomerSales

Please find detailed demo here

Some of user scenarios as below:

1. StackOverflow user scenarios with my answers