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)
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
If I have two Fields one which is used in running total and on the other refresh running total
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
Thanks, its working fine for me..
how did you fix this error
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
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%
how to write same this using while loop
–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
–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
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