SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



SQL Running Total Sample in SQL Server 2008



Calculating sql running total in SQL Server queries is a common task for most of t-sql developers. In order to display running totals for a quantity or amount column, easiest method in SQL Server is using t-sql CTE (Common Table Expression) structures. This t-sql tutorial includes a sample case showing SQL Server running total of ordered product quantity for specific products in SQL Server sample database Adventureworks.



For sql running total sample code in SQL Server, I will use SQL Server 2008 R2 sample database AdventureWorks2008R2. I want to query SalesOrderHeader and SalesOrderDetail sql tables in order to list all orders for a specific range of products, and list order information with order quantity in addition to total number of items ordered till that time by sql running sum.

If you are used to SQL CTE (Common Table Expression) syntax which is new with SQL Server 2005, sql developers will realize that the CTE expression is selecting all data required for SQL Server running total except the value itself.

The sub-select TotalOrderedQty is reading the RunningTotalCTE expression for all previous order items in order to calculate running sum for that specific product.

with RunningTotalCTE as (
select
 top 20
 h.SalesOrderID,
 h.OrderDate,
 d.ProductID,
 d.OrderQty
from Sales.SalesOrderDetail d
inner join Sales.SalesOrderHeader h on d.SalesOrderID = h.SalesOrderID
where ProductID between 771 and 775
)
select
 *,
 TotalOrderedQty = (
  select SUM(OrderQty)
  from RunningTotalCTE c
  where c.SalesOrderID <= RunningTotalCTE.SalesOrderID
  and c.ProductID = RunningTotalCTE.ProductID
 )
from RunningTotalCTE

When you run the above transact-sql query on SQL Server 2008 R2 AdventureWorks database, you will get a similar output in the following screenshot. Please note that the TotalOrderedQty column has values showing running total values on order quantity for the related productid.

T-SQL running total in SQL Server







Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums







Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems