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

Calculate Running Total, Total of a Column and Row

  Author : Shailendra Chauhan
Posted On : 12 Mar 2013
Total Views : 156,630   
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 (1)
Mahesh Saw Reply

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

9 MAR
ASP.NET MVC with Angular2 Development (Online)

09:00 PM - 11:00 PM IST( TUS, THR, SAT)

Know More
6 MAR
Master Class Fast Track MVC 5 with Angular2 Development (Online)

10:30 AM -03:30 PM IST (Fast Track)

Know More
6 MAR
Angular 2 Master Class Fast Track Online Development Training (Online)

10:30 AM- 01:30 PM IST

Know More
27 FEB
ASP.NET MVC with Angular2 Development (Online)

07:00 AM - 09:00 AM IST( MON, WED, FRI)

Know More
25 FEB
ASP.NET MVC with Angular2 Development (Classroom)

05:00 PM-06:30 PM

Know More
25 FEB
Xamarin Forms : Build Cross-platform Apps (Classroom)

8:00 AM-9:30 AM

Know More
25 FEB
ASP.NET MVC with Angular2 Development (Classroom)

3:00 PM-4:30 PM

Know More
14 FEB
.NET Development (Classroom)

11:00 AM-12:00 PM

31 JAN
ASP.NET MVC with Angular2 Development (Online)

07:00 AM-09:00 AM IST(Tuesday & Thursday)

30 JAN
NODEJS & MEAN Stack 2.x Development (Online)

09:00 PM-11:00 PM IST( MON, WED, FRI)

15 JAN
PPC Marketing (Classroom)

04:00 PM-05:30 PM

10 JAN
ASP.NET MVC with AngularJS Development (Online)

09:00 PM-11:00 PM IST on (TUE, THRU,SAT)

31 DEC
ASP.NET MVC with AngularJS Development (Classroom)

09:30 AM-11:00 AM

5 NOV
ASP.NET MVC with AngularJS Development (Classroom)

08:00 AM-09:30 AM

BROWSE BY CATEGORY
 
 
LIKE US ON FACEBOOK
 

Professional Speaks

+