How do I calculate a running total in SQL without using a cursor?

by

You might want to take a look at the update to local variable solution here: http://geekswithblogs.net/Rhames/archive/2008/10/28/calculating-running-totals-in-sql-server-2005—the-optimal.aspx

DECLARE @SalesTbl TABLE (DayCount smallint, Sales money, RunningTotal money)

DECLARE @RunningTotal money

SET @RunningTotal = 0

INSERT INTO @SalesTbl 
SELECT DayCount, Sales, null
FROM Sales
ORDER BY DayCount

UPDATE @SalesTbl
SET @RunningTotal = RunningTotal = @RunningTotal + Sales
FROM @SalesTbl

SELECT * FROM @SalesTbl

Outperforms all other methods, but has some doubts about guaranteed row order. Seems to work fine when temp table is indexed though..

  • Nested sub-query 9300 ms
  • Self join 6100 ms
  • Cursor 400 ms
  • Update to local variable 140 ms

More Related Contents:

  1. Get Parent with multiple children in desc order
  2. How can I schedule a job to run a SQL query daily?
  3. Convert Month Number to Month Name Function in SQL
  4. SQL Server 2012 column identity increment jumping from 6 to 1000+ on 7th entry [duplicate]
  5. Reset identity seed after deleting records in SQL Server
  6. How to avoid the “divide by zero” error in SQL?
  7. Multiple INSERT statements vs. single INSERT with multiple VALUES
  8. Limit on the WHERE col IN (…) condition
  9. Removing leading zeroes from a field in a SQL statement
  10. How to simulate UNPIVOT in Access?
  11. Get day of week in SQL Server 2005/2008
  12. Real life example, when to use OUTER / CROSS APPLY in SQL
  13. Getting result of dynamic SQL into a variable for sql-server
  14. Better techniques for trimming leading zeros in SQL Server?
  15. Simple Query to Grab Max Value for each ID
  16. SQL Server: Extract Table Meta-Data (description, fields and their data types)
  17. SSIS Package not wanting to fetch metadata of temporary table
  18. SQL Select Upcoming Birthdays
  19. SQL GROUP BY CASE statement with aggregate function
  20. How can I include null values in a MIN or MAX?
  21. Favourite performance tuning tricks [closed]
  22. Query a database based on result of query from another database
  23. How to delete the top 1000 rows from a table using Sql Server 2008?
  24. CHECK CONSTRAINT on multiple columns
  25. Cannot create an instance of OLE DB provider Microsoft.Jet.OLEDB.4.0 for linked server null
  26. SQL count consecutive days
  27. Inner Joining three tables
  28. How to execute a stored procedure inside a select query
  29. How to Execute SQL Query without Displaying results
  30. SQL Inner Join On Null Values

Leave a Comment