Shailendra Chauhan
18
JunCalculate Running Total, Total of a Column and Row
Print
2 min read
31 Aug 2022
Intermediate
215K 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.
Take our free skill tests to evaluate your skill!
In less than 5 minutes, with our skill test, you can identify your knowledge gaps and strengths.
