Please enable Javascript to correctly display the contents on Dot Net Tricks!

Calculate Running Total, Total of a Column and Row

 Print 
  Author : Shailendra Chauhan
Posted On : 12 Mar 2013
Total Views : 166,289   
Updated On : 26 Sep 2016
 

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

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 with in 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 with in 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
What do you think?

I hope you will enjoy the tips while writing 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.



Free Interview Books
 
COMMENTS
Mahesh Saw Reply 251 days 6 hours 2 mins ago

It is very Use full for me...Thank You Sir

Manish kumar Reply 298 days 9 hours 48 mins ago

Hello sir, I am learner of sql and java jsp and I have followed your above running balance of DATABASE DATE WISE but MY MS ACCESS DATA BASE NO ACTIVATED TOSE ABOVE ,can you suggest us what type of error , I am posting entire jsp +sql code? can any body solve this SQL code running total, it is not working MS ACCESS Database?

24 SEP
Angular2 and Angular4 (Classroom)
08:30 AM-11:30 AM IST
23 SEP
MEAN Stack (Classroom)
8:30 Am To 11:30 Am
23 SEP
Microsoft Azure Infrastructure Solutions (Online)
08:00 PM-11:00 PM IST / 9:30 AM-12:30 PM CST
20 SEP
MEAN Stack (Online)
07:00 AM-09:00 AM IST
20 SEP
ASP.NET MVC with Angular4 (Online)
9:00PM- 11:00PM IST(+5:30GMT)
16 SEP
Angular2 and Angular4 (Online)
08:00 AM-10:00 AM IST(+5.30 GMT)
7 SEP
ASP.NET MVC with Angular4 (Online)
09:00 PM to 11:00 PM IST (+5:30 GMT)
22 AUG
ASP.NET Core with Angular4 (Online)
07:00 AM - 9:00 AM IST(+5:30 GMT)
19 AUG
MEAN Stack 2 (Online)
5:30 Pm - 7:00 PM IST (+5:30 GMT)
19 AUG
ASP.NET MVC with Angular4 (Online)
10:30 Am to 12:30 PM IST (+ 5:30 GMT)
19 AUG
NodeJS with Angular4 (Classroom)
04:00 PM to 07:00 PM IST (+5:30 GMT)
12 AUG
ASP.NET MVC with Angular4 (Classroom)
11:30 AM - 1:30 PM IST (+5:30GMT)
15 JUL
ASP.NET MVC with Angular4 (Online)
03:30 PM - 05:30 PM IST(+5.30 GMT)
LIKE US ON FACEBOOK
 
+