Thursday, May 15, 2014

Calculating Running Total

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

  1. CREATE TABLE CustomerOrders
  2. (
  3. OrderID int identity,
  4. Amount Decimal(8,2),
  5. OrderDate SmallDatetime default getdate()
  6. )
  7.  
  8. Go
  9. INSERT INTO CustomerOrders(Amount) Values(120.12)
  10. INSERT INTO CustomerOrders(Amount) Values(20.12)
  11. INSERT INTO CustomerOrders(Amount) Values(10.12)
  12. INSERT INTO CustomerOrders(Amount) Values(30.12)
  13. INSERT INTO CustomerOrders(Amount) Values(40)
  14.  
  15. GO
  16. 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

No comments:

Post a Comment