http://www.dotnet-tricks.com/Tutorial/sqlserver/b4I8120313-Calculate-Running-Total,-Total-of-a-Column-and-Row.html

- 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

## No comments:

## Post a Comment