Follow @endyourif rss Facebook LinkedIn

Calculate a Running Total in SQL Server Calculate a Running Total in SQL Server

Published on Jun 16, 2022

Let's imagine a scenario where I have a table that contains a numeric value. When I select the data from this table I want to add another column that contains the running total of all the previous numeric values added together. By leveraging SQL servers two functions: SUM() and OVER() this can be accomplished.



Let's imagine a table called BlogPosts with an Id, CreatedDate, NumberOfComments. I want to calculate the running total of the number of comments on all blog posts for a given day:


SELECT Id
, CreatedDate
,NumberOfComments
, SUM(NumberOfComments)
OVER(ORDER BY CreatedDate ROWS UNBOUNDED PRECEDING) as RunningTotal
FROM [BlogPosts]


For each row in the blog post table it will list the blog, the date it was created, the number of comments on that particular post and finally the SUM of the number of comments for a given day.

Tags: SQL | sum() | mssql

My Books
ASP.NET MVC 5 With Bootstrap and Knockout.js
Knockout.js Building Dynamic Client-Side Applications
20 Recipes for Programming MVC 3
20 Recipes for Programming PhoneGap
Rapid Application Development with CakePHP