Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

Calculate Running Total in SQL

  • Last Updated : 27 Apr, 2021

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:

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

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;
IDSALARYNAMEDEPT_ID
134000ANURAGUI DEVELOPER
233000HARSHBACKEND DEVELOPERS
336000SUMITBACKEND DEVELOPERS
436000RUHIUI DEVELOPER
537000KAEUI 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: 

IDSALARYNAMEDEPT_IDRunning Total
134000ANURGUI DEVELOPER34000
233000HARSHBACKEND DEVELOPERS67000
336000SUMITBACKEND DEVELOPERS103000
436000RUHIUI DEVELOPER139000
537000KAEUI DEVELOPER176000

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:

IDSALARYNAMEDEPT_IDRunning Total
134000ANURAGUI DEVELOPER34000
233000HARSHBACKEND DEVELOPERS67000
336000SUMITBACKEND DEVELOPERS103000
436000RUHIUI DEVELOPER139000
537000KAEUI DEVELOPER176000

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: 

IDSALARYNAMEDEPT_IDRunning_Total
233000HARSHBACKEND DEVELOPERS33000
336000SUMITBACKEND DEVELOPERS69000
134000ANURAGUI DEVELOPER34000
436000RUHIUI DEVELOPER70000
537000KAEUI DEVELOPER107000

Select in SQL Server Management Studio:

My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!