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)
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
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
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: