Calculate Running Total, Total of a Column and Row in sql server

Introduction

Many times, you required to show information of each transaction and also keep a Running Total and Final Total like GridView in Asp.Net. In this article, I am going to explain, how can you achieve this using SQL Query in simple and easy way.

Suppose you have the below CustomerOrders table and has the data as shown below:

CREATE TABLE CustomerOrders     (     OrderID int identity,     Amount Decimal(8,2),     OrderDate SmallDatetime default getdate()     )          Go     INSERT INTO CustomerOrders(Amount) Values(120.12)     INSERT INTO CustomerOrders(Amount) Values(20.12)     INSERT INTO CustomerOrders(Amount) Values(10.12)     INSERT INTO CustomerOrders(Amount) Values(30.12)     INSERT INTO CustomerOrders(Amount) Values(40)          GO     SELECT * FROM CustomerOrders

Calculating Running Total

select OrderID, OrderDate, CO.Amount     ,(select sum(Amount) from CustomerOrders     where OrderID <= CO.OrderID)     'Running Total'     from CustomerOrders CO

Calculating Final Total

SELECT OrderID, SUM(Amount) AS Amount     FROM CustomerOrders     GROUP BY OrderID WITH ROLLUP

Calculating Total of All Numeric columns in a row

SELECT OrderID, Amount, SUM(OrderID+Amount) AS RowNumericColSum     FROM CustomerOrders     GROUP BY OrderID,Amount     ORDER BY OrderID