Calculate Running Total, Total of a Column and Row

Shailendra Chauhan  Print   1 min read
12 Mar 2013
26 Sep 2016
Intermediate
185K

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
```