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 : 175,953
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

Anjan Bose 211 days 9 hours 24 mins ago

Calculating Final Total

SELECT coalesce(convert(varchar(max),OrderID),'Total') as ORDID, SUM(Amount) AS Amount

FROM CustomerOrders

GROUP BY OrderID WITH ROLLUP

This may look more good.

Mahesh Saw 429 days 17 hours 28 mins ago

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

Manish kumar 476 days 21 hours 14 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?

SUBSCRIBE TO OUR YOUTUBE CHANNEL

LIKE US ON FACEBOOK

+