forum

Home / DeveloperSection / Forums / SQL Query to Calculate Running Total in SQL Server

Ask Question
Anubhav Kumar

Anubhav Kumar

Follow
The Anubhav portal was launched in March 2015 at the behest of the Hon'ble Prime Minister for retiring government officials to leave a record of their experiences while in Govt service .

Can you answer this question?

Answer

1 Answers

Ravi Vishwakarma

Ravi Vishwakarma

16-Jul-2024

To calculate a running total in SQL Server, you can use the SUM function along with the OVER clause. 

Here’s an example query that calculates a running total of the Amount column in a table called Transactions:

Example Table Schema

Assume you have a table named Transactions with the following schema:

CREATE TABLE Transactions (
    TransactionID INT PRIMARY KEY,
    TransactionDate DATE,
    Amount DECIMAL(10, 2)
);

Insert data to Table

INSERT INTO Transactions (TransactionID, TransactionDate, Amount) VALUES
(1, '2023-01-01', 100.00),
(2, '2023-01-02', 150.00),
(3, '2023-01-03', 200.00),
(4, '2023-01-04', 250.00),
(5, '2023-01-05', 300.00),
(6, '2023-01-06', 350.00),
(7, '2023-01-07', 400.00),
(8, '2023-01-08', 450.00),
(9, '2023-01-09', 500.00),
(10, '2023-01-10', 550.00),
(11, '2023-01-11', 600.00),
(12, '2023-01-12', 650.00),
(13, '2023-01-13', 700.00),
(14, '2023-01-14', 750.00),
(15, '2023-01-15', 800.00),
(16, '2023-01-16', 850.00),
(17, '2023-01-17', 900.00),
(18, '2023-01-18', 950.00),
(19, '2023-01-19', 1000.00),
(20, '2023-01-20', 1050.00);

Sample Query

Here's how you can calculate the running total:

SELECT 
    TransactionID, 
    TransactionDate, 
    Amount, 
    SUM(Amount) OVER (ORDER BY TransactionDate) AS RunningTotal
FROM 
    Transactions
ORDER BY 
    TransactionDate;

Explanation:

  • SELECT TransactionID, TransactionDate, Amount: Selects the columns to display.
  • SUM(Amount) OVER (ORDER BY TransactionDate) AS RunningTotal:
    • SUM(Amount) calculates the sum of the Amount column.
    • OVER (ORDER BY TransactionDate) specifies the order in which the rows are processed. It calculates the running total for each row in the order of TransactionDate.
    • AS RunningTotal names the calculated column as RunningTotal.
  • FROM Transactions: Specifies the table to query.
  • ORDER BY TransactionDate: Orders the final result set by TransactionDate.

This query will produce a result set with each row showing the TransactionID, TransactionDate, Amount, and the RunningTotal up to that row, ordered by TransactionDate.

Or

SELECT  
    TransactionDate, 
    SUM(Amount) AS RunningTotal
FROM Transactions
GROUP BY TransactionDate

This query will produce a result set with each row showing the TransactionDate, and the RunningTotal up to that row.

Read more

Describe Common Table Expressions (CTEs) in SQL server.

How to Join Multiple Tables and Retrieve Specific Columns in SQL Server?

Implement row-level security in SQL Server to restrict access to data to users.

MindStick Training

Liked By