Running total in an SQL query

There are no standard functions in either MySQL or MS SQL Server to return a running total for an SQL query. It is, however, relatively easy using the CROSS JOIN statement. In order to do this, use the following SQL:

SELECT column_names, SUM(b.running_total_column) AS RunningTotal
FROM table_name a CROSS JOIN table_name b
WHERE (b.unique_id <= a.unique_id)
GROUP BY a.unique_id,a.running_total_column

Note that the performance of this query can be improved by adding a clustered index on the unique_id field. Also, if you include extra statements in the WHERE clause, you need to limit the query for both the a and b tables (ie instead of WHERE a.id < 100, you need WHERE a.id < 100 AND b.id < 100).

An alternative (and more efficient) method is the following stored procedure (but obviously you do not have this option in MySQL).

CREATE TABLE #TableName (unique_id int, running_total_column int, RunningTotal int)

DECLARE @unique_id int,
@running_total_column int,
@RunningTotal int

SET @RunningTotal = 0

DECLARE rt_cursor CURSOR FOR
   SELECT unique_id, running_total_column FROM tablename

OPEN rt_cursor

FETCH NEXT FROM rt_cursor INTO @unique_id,@running_total_column

WHILE @@FETCH_STATUS = 0
BEGIN
  SET @RunningTotal = @RunningTotal + @running_total_column
  INSERT #TableName VALUES (@unique_id,@running_total_column,@RunningTotal)
FETCH NEXT FROM rt_cursor INTO @unique_id,@running_total_column
END

CLOSE rt_cursor
DEALLOCATE rt_cursor

SELECT * FROM #TableName ORDER BY unique_id

DROP TABLE #TableName

As with all the code, you will need to change the text in italics to the columns / table names you require.

You might also like...

Comments

James Crowley James first started this website when learning Visual Basic back in 1999 whilst studying his GCSEs. The site grew steadily over the years while being run as a hobby - to a regular monthly audience ...

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Walking on water and developing software from a specification are easy if both are frozen.” - Edward V Berard