SQL SERVER – How to Find Running Total in SQL Server

Finding running total is one of the most popular request user encounters in the industry. There are two different ways to find out running totals. One of the methods is as per SQL Server 2008 R2 and earlier version. This is indeed a very expensive version of finding running total and I always hated this solution when I had to implement it in the industry. However, I am extremely delighted since SQL Server 2012 as it has a new feature of OVER ORDER BY ROW methods. It is much more efficient and cleaner to implement.

Let us first create a sample table and populate the same.

USE tempdb
GO
CREATE TABLE TestTable (ID INT, Value INT)
INSERT INTO TestTable (ID, Value)
SELECT 1, 10
UNION ALL
SELECT 2, 20
UNION ALL
SELECT 3, 30
UNION ALL
SELECT 4, 40
UNION ALL
SELECT 5, 50
UNION ALL
SELECT 6, 60
UNION ALL
SELECT 7, 70
GO
-- selecting table
SELECT ID, Value
FROM TestTable
GO

Here is the screenshot of the resultset.

Here is the query which you can execute on SQL Server 2008 R2 or earlier version. The query is very expensive.

-- Running Total for SQL Server 2008 R2 and Earlier Version
SELECT ID, Value,
(
SELECT SUM(Value)
FROM TestTable T2
WHERE T2.ID <= T1.ID) AS RunningTotal
FROM TestTable T1
GO

Here is the query which you can execute on SQL Server 2012 or later version. The query is very efficient.

-- Running Total for SQL Server 2012 and Later Version
SELECT ID, Value,
SUM(Value) OVER(ORDER BY ID ROWS UNBOUNDED PRECEDING) AS RunningTotal
FROM TestTable
GO

Both of the above query returns following results.

If there is any other better option, please share it here.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – GROUP BY Columns with XMLPATH – Comma Delimit Multiple Rows
Next Post
SQL SERVER – How to Catch Errors While Inserting Values in Table

Related Posts

No results found.

11 Comments. Leave new

  • THANKS FOR 2012 QUERY.WE CAN ALSO DO BY RECURSIVE CTE.

    ;with CTE as
    (select id,Value from @TestTable where ID=1
    union all
    select tt.id,tt.value+c.Value
    from @TestTable tt inner join cte c on tt.id-c.id=1
    WHERE TT.ID<=7
    )

    select * from CTE

    Reply
  • If I have two Fields one which is used in running total and on the other refresh running total

    Reply
  • Hi,

    I tried below query to get running total.

    — Running Total for SQL Server 2012 and Later Version
    SELECT ID, Value,
    SUM(Value) OVER(ORDER BY ID ROWS UNBOUNDED PRECEDING) AS RunningTotal
    FROM TestTable
    GO

    but I got below error:
    Msg 102, Level 15, State 1, Line 26
    Incorrect syntax near ‘ROWS’.

    I am using SQL server 2014….
    Note: the SQL server version upgraded from 2012 to 2014

    Reply
  • what if you have no unique identifier (ID) as its part of a larger with statement and its in the summary section – for example

    WITH ProgDate AS
    (SELECT a.NewDemandNo AS ProgDemandNo
    ,a.DemandDate AS ProgDemandDate
    ,a.DemandProgressDate
    ,a.State AS PRState
    ,COALESCE (MIN(b.DemandProgressDate), a.DemandProgressDate) PrNextDate
    ,CASE WHEN a.ProgressType = ‘Issued’ THEN a.DemandProgressDate ELSE NULL END AS PRIssuedDate
    ,CASE WHEN a.ProgressType = ‘State’ THEN a.DemandProgressDate ELSE NULL END AS PRStateDate
    ,CASE WHEN a.ProgressType = ‘Cleared’ THEN a.DemandProgressDate ELSE NULL END AS PRClearedDate
    ,a.ProgressType
    ,a.ProgressYear
    ,a.ProgressMonth
    ,a.ProgressDay
    FROM Capability_Dev.ODS_Taps_DemandProgress a
    LEFT OUTER JOIN Capability_Dev.ODS_Taps_DemandProgress b
    ON a.NewDemandNo = b.NewDemandNo
    AND a.DemandDate = b.DemandDate
    AND b.DemandProgressDate > a.DemandProgressDate
    GROUP BY a.NewDemandNo,a.DemandDate,a.DemandProgressDate,a.State
    ,a.ProgressYear,a.ProgressMonth,a.ProgressDay,a.ProgressType
    )
    ,PRBase AS
    (SELECT ProgDate.ProgDemandNo
    ,ProgDate.ProgDemandDate
    ,ProgDate.DemandProgressDate
    ,Case WHEN ProgDate.ProgressType = ‘Cleared’ THEN d.CurrentState ELSE ProgDate.PRState END AS PRState
    ,Case WHEN (ProgDate.ProgressType = ‘Cleared’ AND d.ClearanceCode = ‘CAN’) THEN ProgDate.DemandProgressDate ELSE NULL END AS PRCancelDate
    ,ProgDate.PrNextDate
    ,ProgDate.ProgressType
    ,ProgDate.ProgressYear
    ,ProgDate.ProgressMonth
    ,ProgDate.ProgressDay
    ,ProgDate.PRIssuedDate
    ,ProgDate.PRClearedDate
    ,d.ClearanceDate AS DmdEndDate
    ,d.ClearanceCode AS DmdEndCode
    ,d.Action AS DmdAction
    ,d.ForwardDepth
    ,d.Location AS DmdLoc
    ,DATEDIFF(day, ProgDate.ProgDemandDate, d.ClearanceDate) AS TotalTime
    ,DATEDIFF(day, ProgDate.PRIssuedDate, d.ClearanceDate) AS DeliveryTime
    ,CASE WHEN (d.ClearanceCode = ‘CAN’ AND (DatePart(year,ProgDate.ProgDemandDate) = DatePart(year,ProgDate.PRClearedDate)) AND (DatePart(MONTH,ProgDate.ProgDemandDate) = DatePart(MONTH,ProgDate.PRClearedDate)) ) THEN 1 ELSE 0 END AS CancelledInMonth
    FROM Capability_Dev.ODS_TAPS_Demands d
    INNER JOIN ProgDate
    ON d.NewDemandNo = ProgDate.ProgDemandNo
    AND d.DemandDate = ProgDate.ProgDemandDate
    )
    , Dates AS
    (Select Date_Only, DayOfMonth, BAE_Workday, MonthOfYear, Year, QuarterOfYear
    From CA_Reference.CA_Developers.DIM_Date
    Where DATEDIFF(Year,CALENDAR_DATE , CURRENT_TIMESTAMP) < 2
    AND CALENDAR_DATE <= CURRENT_TIMESTAMP
    )
    , PRCalc As
    (Select Dates.Year
    ,Dates.MonthOfYear
    ,Dates.DayOfMonth
    ,PRBase.PRState
    ,SUM (CASE WHEN (PRBASe.DemandProgressDate IS NOT NULL AND ProgressType IN ('Issued','State')) THEN 1 ELSE 0 END) AS SumPRStates

    ,SUM (CASE WHEN (PRBASe.DemandProgressDate IS NOT NULL AND PRBase.DmdEndCode = 'CAN') THEN 1 ELSE 0 END) AS SumPRCancel
    From PRBase
    Left Outer Join Dates
    ON PRBase.ProgressYear = Dates.Year
    AND PRbase.ProgressMonth = Dates.MonthOfYear
    AND PRBase.ProgressDay = Dates.DayOfMonth
    WHERE Dates.Year = DatePart(Year,PRBase.DemandProgressDate)
    Group By Dates.Year, Dates.MonthOfYear, Dates.DayOfMonth, PRBase.PRState
    )
    Select * From PRCalc

    in PrCalc i need to create a running total of the daily totals created

    Reply
  • scott bellis
    June 13, 2016 10:17 pm

    When running the 2 queries, the execution plan shows that the later (i.e. 2012 SQL) actually costs twice as much as the 2008 version

    67% vs 33%

    Reply
  • how to write same this using while loop

    Reply
  • –How would you create a running total if you have another column called Credit that changes the sign of the Value to a negative if true?
    SELECT ID, Value, Credit,
    CASE WHEN Credit = 1 then (-Value) ELSE Value END AS NetValue,
    –and now I’m lost because the value below is no longer correct.
    SUM(Value) OVER(ORDER BY ID ROWS UNBOUNDED PRECEDING) AS RunningTotal
    FROM TestTable
    GO

    Reply
  • –This seems to work for me:
    ,SUM(CASE Credit
    When 1 then -Value
    When 0 then Value
    END) OVER(ORDER BY ID ROWS UNBOUNDED PRECEDING) AS RunningTotal

    Reply
  • what About Running total Conditional Sum Suppose we Passed Parameter to Query Running total <100 Then It Only Show Three Record ie id-1,id-2,id-3 Please Tell Me

    Reply

Leave a Reply

Menu
Exit mobile version