This post is related with sharing my thoughts on calculating Running Total in Sql Server.
There are various ways to calculate running total in Sql Server like
- Using Nested Sub Query
- Using Self Join
- Using Cursors
Cumulative Sum in SQL Server
Refer related post for SQL Server 2012,
Cumulative Sum in SQL Server 2012
But, In this post I am sharing how running total can be calculated using Common Table Expression (CTE). As far I believe, this is the most efficient way of calculating running total in Sql Server. Since, we are using CTE to calculate running total and CTE was introduced in Sql Server 2005. This way of calculating running total would work with Sql Server 2005 onwards. Let's take an example. Suppose we have a table Employee and we want to calculate running total of Salary Column of the Employee Table
Script to prepare data for Table Employee
CREATE TABLE TBLEMPLOYEE ( EMPID CHAR(6) , EMPNAME VARCHAR(50) , SALARY INT ) INSERT INTO TBLEMPLOYEE SELECT 'EMP001', 'Ajay Kumar', 30000 UNION ALL SELECT 'EMP002', 'Amit Sharma', 20000 UNION ALL SELECT 'EMP003', 'Sumit Jain', 25000 UNION ALL SELECT 'EMP004', 'Rohit Gupta', 25000 SELECT * FROM TBLEMPLOYEE
Script to calculate Running Total of Salary Column from the Employee Table using CTE.
DECLARE @TAB TABLE( EMPID CHAR(6) , EMPNAME VARCHAR(50) , SALARY INT , RUNNING_TOTAL INT ) INSERT INTO @TAB (EMPID, EMPNAME, SALARY) SELECT EMPID, EMPNAME, SALARY FROM TBLEMPLOYEE DECLARE @RUNNING_TOTAL INT SET @RUNNING_TOTAL = 0 ;WITH RUNNING_TOAL AS ( SELECT EMPID, EMPNAME, SALARY, RUNNING_TOTAL FROM @TAB ) UPDATE RUNNING_TOAL SET @RUNNING_TOTAL = RUNNING_TOTAL = @RUNNING_TOTAL + SALARY SELECT * FROM @TAB