SQL Server 2012 - Running total with SUM function‏

Continuing my series on SQL Server 2012, today we will learn about running totals in SQL Server 2012 using the SUM function. Running total in SQL Server is usually achieved with co-related subquery or a cursor, in versions prior to SQL 2012. But from 2012 onwards, we can use the SUM function with ORDER BY clause.

Consider the following set of data

create table #t(emp_id varchar(10), sales_date datetime, sales_amount decimal(12,2))
GO
insert into #t (emp_id,sales_date,sales_amount)
select 'E001','20120101',300.00 union all
select 'E001','20120107',400.00 union all
select 'E001','20120304',1200.50 union all
select 'E002','20121110',4000.50 union all
select 'E002','20121119',200.50 union all
select 'E003','20121001',1000.00 union all
select 'E003','20121222',450.50 union all
select 'E003','20121121',2000.50 union all
select 'E004','20120403',1200.00 union all
select 'E004','20120509',1350.50 union all
select 'E004','20120502',800.50 union all
select 'E004','20120810',200.00

Now execute the following query

select
    emp_id,sales_date,sales_amount,
    sum(sales_amount) over (partition by emp_id order by sales_date rows unbounded preceding) as running_sales_amount
from #t

The result is shown below

The column running_sales_amount will have running total of sales_amount for each emp_id. Rows unbounded preceding indicates that the running total starts at first row for each partition.

Note : If there is no partition clause, the running total will not be reset and carried forward to the entire row set.

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan