Calculate Running Total, Total of a Column and Row

1.  CREATE TABLE CustomerOrders

2.  (

3.   OrderID int identity,

4.   Amount Decimal(8,2),

5.   OrderDate SmallDatetime default getdate()

6.   )

7.   

8.  Go

9.   

10.  INSERT INTO CustomerOrders(Amount) Values(120.12)

11.  INSERT INTO CustomerOrders(Amount) Values(20.12)

12.  INSERT INTO CustomerOrders(Amount) Values(10.12)

13.  INSERT INTO CustomerOrders(Amount) Values(30.12)

14.  INSERT INTO CustomerOrders(Amount) Values(40)

15.  

16. GO

17.  

18.  SELECT * FROM CustomerOrders

Calculating Running Total

Let's see how to calculate the running total using SQL Query as given below:

1.   select OrderID, OrderDate, CO.Amount

2.   ,(select sum(Amount) from CustomerOrders

3.   where OrderID <= CO.OrderID)

4.   'Running Total'

5.  from CustomerOrders CO

Calculating Final Total

Let's see how to calculate the final total using ROLLUP with in SQL Query as given below:

1.  SELECT OrderID, SUM(Amount) AS Amount

2.  FROM CustomerOrders

3.  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:

1.  SELECT OrderID, Amount, SUM(OrderID+Amount) AS RowNumericColSum

2.  FROM CustomerOrders

3.  GROUP BY OrderID,Amount

4.  ORDER BY OrderID