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.
Comments