Calculate Running Total in SQL

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;

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: 

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:

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: 

Select in SQL Server Management Studio:

K

khushboogoyal499

Follow

Improve

Article Tags :

• SQL
• DBMS-SQL