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.