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))
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
Now execute the following query
sum(sales_amount) over (partition by emp_id order by sales_date rows unbounded preceding) as running_sales_amount
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.
Did you like this post?
|subscribe via rss||subscribe via e-mail|
|print this post||follow me on twitter|