SQL Tip #2 – Running total

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

RunningTotal

Like my all other sql tips, today also I am going to show you different  ways  to achieve this :

SQL 1:

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

SQL 2:

SELECT TOP 1000 *,
(
    SELECT SUM(Salary)
    FROM Employee
    WHERE EmployeeId <= MainEmp.EmployeeId
 ) AS RunningTotal
FROM Employee MainEmp

SQL 3:

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

 

SQL 4:

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….

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s