Today it is about running total.
Running totals are sum of values that grows as new line item is introduced in a table. Or simply, we can say – a total that continually changes for added rows. This is also called partial sum. Running total in a row is calculated by adding a value from the current row to the running total of the previous row.
Consider the example below:
Running total Salary = Current Salary + Previous row’s running total Salary
Like my all other sql tips, today also I am going to show you different ways to achieve this :
SELECT TOP 1000 Main.*, RunningTotal = SUM(Sub.Salary) FROM Employee AS Main INNER JOIN Employee AS Sub ON Sub.EmployeeId <= Main.EmployeeId GROUP BY Main.EmployeeId,Main.EmployeeName,Main.Salary ORDER BY Main.EmployeeId
SELECT TOP 1000 *, ( SELECT SUM(Salary) FROM Employee WHERE EmployeeId <= MainEmp.EmployeeId ) AS RunningTotal FROM Employee MainEmp
SELECT TOP 1000 MainEmp.*,SubEmp.Salary AS RunningTotal FROM Employee MainEmp CROSS APPLY ( SELECT SUM(Salary) AS Salary FROM Employee WHERE EmployeeId <= MainEmp.EmployeeId ) AS SubEmp
SELECT TOP 1000 Main.*, RunningTotal = Salary + ISNULL( ( SELECT SUM(Salary) FROM Employee AS Sub WHERE Sub.EmployeeId < Main.EmployeeId), 0) FROM Employee AS Main ORDER BY EmployeeId
To be continued….