Sunday, February 19, 2012

Running Total in Sql Server


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
Refer related post to calculate cummulative sum using row_number,
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
OUTPUT



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
OUTPUT

0 comments:

Post a Comment