Cumulative Sum or Running Total is a sequence of
partial sums of a given sequence which is used to display the total sum of data
as it grows with time or any other series or progression. This lets us know and
view the total contribution so far of a given measure against a given sequence or
time.
In SQL Server, getting running totals in T-SQL
is not hard task because there are different ways of calculating cumulative sums or
running totals. To understand it in a better way, we are taking an employee
example to accumulate their salaries across the company as well as accumulate their
salary within their departments also.
Demo data
of Employee Master – We
are using table variable to view the total contribution so far of a given
measure against a given sequence or time as given below:
------ Declare table varible
DECLARE @EmpSalary TABLE
(
Id INT IDENTITY(1,1),
EmpId INT,
DeptId INT,
Salary FLOAT
)
----- Insert values into @EmpSalary
INSERT INTO @EmpSalary(EmpId, DeptId, Salary)
VALUES
(101,10,25000), (102,10,35000),
(103,11,15000), (104,11,18500)
---- Pull result from @EmpSalary
SELECT Id, EmpId, DeptId, Salary
FROM @EmpSalary
|
Expected Output for Cumulative Sum or Running Total
By using T-SQL in SQL Server, we can get the
expected result as given below:
Running Salary across the data table
Running Salary across the data table within
department
|
Cumulative Sum or Running Totals in SQL Server 2005 or 2008 R2
SQL Server supports most of the aggregation
functions such as SUM and AVG in this context with the exceptions of grouping
and we can use self-join within the table, nested select statements to pull the
cumulative sum or running total in SQL Server 2005 or 2008 R2 as given below:
----- Running Salary across the
data table
SELECT E.Id, E.EmpId, E.DeptId, E.Salary,
SUM(S.Salary) As RunningSalary
FROM @EmpSalary E
---- self-join
INNER JOIN @EmpSalary S on E.Id>=S.Id
GROUP BY E.Id,
E.EmpId, E.DeptId, E.Salary
ORDER BY E.Id,Sum(S.Salary)
----- Running
Salary across the data table within department
SELECT E.Id, E.EmpId, E.DeptId
,E.Salary, E.RunningSalary
,Sum(D.Salary) As
RunningDeptSal
FROM
----- Drived data table
(
----- Pull Running Total Salary accross the data
SELECT E.Id, E.EmpId, E.DeptId, E.Salary,
SUM(S.Salary) As RunningSalary
FROM @EmpSalary E
---- self join
INNER JOIN @EmpSalary S on E.Id>=S.Id
GROUP BY E.Id,
E.EmpId, E.DeptId, E.Salary
)E
---- Self join within dept
INNER JOIN @EmpSalary D on E.Id>=D.Id
AND E.DeptId=D.DeptId
----- group all column of derived table
GROUP BY E.Id,
E.EmpId, E.DeptId,
E.Salary, E.RunningSalary
ORDER BY E.Id,Sum(D.Salary)
|
The above queries calculates a cumulative sum of salary per department and ORDER BY Id and Aggregate sum () function. The rows are cross joined restricting the join only to equal or smaller ID values in right table.
Cumulative
Sum or Running Totals in SQL Server 2012 on wards
In SQL Server 2012 on wards, more complex
business problems such as running totals or cumulative sums could be solved
without the extensive use of cursors or nested select statement. We can use OVER
clause, PARTITION BY with the GROUP BY clause and Aggregates function to pull running
totals or cumulative sums against each row as given below:
----- SQL Server 2012 Onwards
----- Running Salary across the
data table
SELECT E.Id, E.EmpId, E.DeptId, E.Salary,
SUM(E.Salary) Over
(Order
by E.Id)
As RunningSalary
FROM @EmpSalary E
GROUP BY E.Id,
E.EmpId, E.DeptId, E.Salary
ORDER BY E.Id,Sum(E.Salary)
----- Running
Salary across the data table within department
SELECT E.Id, E.EmpId, E.DeptId, E.Salary,
SUM(E.Salary) Over
(Order
by E.Id)
As RunningSalary,
---- Use Over with Partition By and Order By
SUM(E.Salary) Over
(Partition By
E.DeptId
Order by
E.Id) As RunningDeptSal
FROM @EmpSalary E
GROUP BY E.Id,
E.EmpId, E.DeptId, E.Salary
ORDER BY E.Id,Sum(E.Salary)
Note: This works with SQL Server
2012 and up, 2008 has limited support for window functions.
|
We can see here that the OVER clause allows us to manage the grouping based on the context specified in relationship to the current row. With the expansion of the OVER clause to include PARTITION BY and ORDER BY support with aggregates, window functions increased their value substantially in SQL Server 2012 on wards.
To learn more, please visit us at YouTube -
Conclusion
There are several ways but it depends on your
SQL Server version to choose the best approach to pull the cumulative sum or
running total against each row. If the running total needs to be calculated to
different partitions of data, just to use more conditions in PARTITION BY
clause, ORDER BY clause in the OVER clause.
No comments:
Post a Comment