Browse Articles

Calculate Running Total, Total of a Column and Row

31 Aug 2022
Intermediate
217K Views

Many times, you required to show information on 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 a 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

Let's see how to calculate the running total using SQL Query as given below:

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

Calculating Final Total

Let's see how to calculate the final total using ROLLUP within SQL Query as given below:

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

Calculating Total of All Numeric columns in a row

Let's see how to calculate the total of all numeric fields within a row using SQL Query as given below:

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

Read More Articles Related to SQL Server

What do you think?

I hope you will enjoy the tips while writing a query in SQL Server. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.

Learn to Crack Your Technical Interview

Accept cookies & close this