Open In App

Calculate Running Total in SQL

Improve
Improve
Like Article
Like
Save
Share
Report

Structured Query Language or SQL is a standard Database language that is used to create, maintain and retrieve the data from relational databases like MySQL, Oracle, etc. In this article, we will be using the Microsoft SQL Server.
Here we are going to see how to get the running salary total of each department. Here, we will first create a database named “geeks” then we will create a table “department” in that database. After, that we will execute our query on that table.

Creating Database:

CREATE geeks;

To use this database:

USE geeks;

This is our table in the geek’s database:

CREATE TABLE department(
ID int,
SALARY int,
NAME Varchar(20),
DEPT_ID Varchar(255));

Output:
Command(s) completed successfully.

Add value into the table:

INSERT INTO department 
VALUES (1, 34000, 'ANURAG', 'UI DEVELOPERS');

INSERT INTO department 
VALUES (2, 33000, 'harsh', 'BACKEND DEVELOPERS');

INSERT INTO department 
VALUES (3, 36000, 'SUMIT', 'BACKEND DEVELOPERS');

INSERT INTO department 
VALUES (4, 36000, 'RUHI', 'UI DEVELOPERS');

INSERT INTO department 
VALUES (5, 37000, 'KAE', 'UI DEVELOPERS');

Output:
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)

Select in SQL Server Management Studio:

This is our data inside the table:

SELECT * FROM department;
ID SALARY NAME DEPT_ID
1 34000 ANURAG UI DEVELOPER
2 33000 HARSH BACKEND DEVELOPERS
3 36000 SUMIT BACKEND DEVELOPERS
4 36000 RUHI UI DEVELOPER
5 37000 KAE UI DEVELOPER

Select in SQL Server Management Studio:

Example 1:
Query to Calculate Running Total in SQL Server

SELECT * ,(
SELECT SUM(T2.[SALARY])  
 FROM [department] AS T2
       WHERE T2.[ID] <= T1.[ID]
) AS [Running Total]
FROM [department] AS T1

Output: 

ID SALARY NAME DEPT_ID Running Total
1 34000 ANURG UI DEVELOPER 34000
2 33000 HARSH BACKEND DEVELOPERS 67000
3 36000 SUMIT BACKEND DEVELOPERS 103000
4 36000 RUHI UI DEVELOPER 139000
5 37000 KAE UI DEVELOPER 176000

Select in SQL Server Management Studio:

Example 2
In this SQL Server example, we’ll use the SUM Function and OVER to find the Running Total.
Query to Calculate Running Total in SQL Server

SELECT *
    ,SUM([SALARY]) OVER (
           ORDER BY  [ID]
  ) AS [Running Total]
FROM department

Output:

ID SALARY NAME DEPT_ID Running Total
1 34000 ANURAG UI DEVELOPER 34000
2 33000 HARSH BACKEND DEVELOPERS 67000
3 36000 SUMIT BACKEND DEVELOPERS 103000
4 36000 RUHI UI DEVELOPER 139000
5 37000 KAE UI DEVELOPER 176000

Select in SQL Server Management Studio:

Example 3:
In this SQL Server example,  we will use PARTITION BY with OVER to find the Running Total.
Query to Calculate Running Total in SQL Server

SELECT *
   ,SUM([SALARY]) OVER  (
PARTITION BY DEPT_ID  ORDER BY Id
) AS [Running Total]
FROM department

Output: 

ID SALARY NAME DEPT_ID Running_Total
2 33000 HARSH BACKEND DEVELOPERS 33000
3 36000 SUMIT BACKEND DEVELOPERS 69000
1 34000 ANURAG UI DEVELOPER 34000
4 36000 RUHI UI DEVELOPER 70000
5 37000 KAE UI DEVELOPER 107000

Select in SQL Server Management Studio:


Last Updated : 27 Apr, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads