Running Total in Sql Server

This post is related with sharing my thoughts on calculating Running Total in Sql Server.

In this post I am sharing how running total can be calculated using recursive Common Table Expression (CTE).

NOTE: Solution would work with SQL Server 2005 onward

Example
Suppose we have Employee Table and we would calculate running total of Salary Column

Let's first create Employee Table and populate some data in it
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

Now lets write script to calculate Running Total of Salary Column from the Employee Table using recursive 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 = 0
;WITH RUNNING_TOTAL AS (
    SELECT    EMPID, EMPNAME, SALARY, RUNNING_TOTAL
    FROM    @TAB
)
UPDATE RUNNING_TOTAL
SET  @RUNNING_TOTAL = RUNNING_TOTAL = @RUNNING_TOTAL + SALARY

SELECT * FROM @TAB
OUTPUT

Refer related post to calculate cumulative sum using row_number,
Cumulative Sum in SQL Server

Refer related post for SQL Server 2012,
Cumulative Sum in SQL Server 2012
Running Total in Sql Server Running Total in Sql Server Reviewed by Sandeep Mittal on Sunday, February 19, 2012 Rating: 5

No comments:

Powered by Blogger.