T-SQL Ninja #17

Running Totals

What are Running Totals?

A Running Total is a scenario in which each row contains the sum of the values of a specific column of the previous rows. A good example of this is when points from different games are added up. Of course, it is important for a running total that the rows are run through in an unambiguous sorting, otherwise any values will come up in our calculation.

How can you calculate a Running Total?

A running total is similar to the RANK and ROW_NUMBER functions already discussed. It is a window function, i.e. a function that operates on a certain set of rows and combines them. So we have a Randori of four teams over several rounds and count the points of the teams in each round. We store these points in a table:

DROP TABLE IF EXISTS #games 

CREATE TABLE #games
(
     RandoriNumber INT NOT NULL IDENTITY(1,1)
    ,Team1Score INT NOT NULL DEFAULT 0
    ,Team2Score INT NOT NULL DEFAULT 0
    ,Team3Score INT NOT NULL DEFAULT 0
    ,Team4Score INT NOT NULL DEFAULT 0
)

We have stored a battle number (RandoriNumber) in this table, according to which we can sort the lines uniquely afterwards. Now we fill the results into the table over a training cycle with 5 fights:

DECLARE @nRows int 
SELECT @nRows = COUNT(*) FROM #games

WHILE @nRows < 5 BEGIN
    INSERT INTO #games 
    (Team1Score, Team2Score, Team3Score, Team4Score)
    VALUES
    (CAST(10*RAND()-1 AS INT),  CAST(10*RAND()-1 AS INT), CAST(10*RAND()-1 AS INT),  CAST(10*RAND()-1 AS INT))
    SELECT @nRows = COUNT(*) FROM #games
END

We add random numbers from zero to ten to the table here, so your results will look different from the ones we show here. Now we would like to know for each competition, what the score of each team was on that day. For this purpose, we calculate the sum of the four team scores for each row, sorted by the Randori number. This is very easy, because we only have to give the SUM function an OVER (ORDER BY) window for this.

SELECT 
     RandoriNumber
    ,Team1Score
    ,Team2Score
    ,Team3Score
    ,Team4Score
    ,Team1RunningTotal = SUM(Team1Score) OVER (ORDER BY RandoriNumber)
    ,Team2RunningTotal = SUM(Team2Score) OVER (ORDER BY RandoriNumber)
    ,Team3RunningTotal = SUM(Team3Score) OVER (ORDER BY RandoriNumber)
    ,Team4RunningTotal = SUM(Team4Score) OVER (ORDER BY RandoriNumber)
FROM #games 

The result should look something like this:

You see at Team1 in line 2 as Running Total the value 5, which is composed of the score 5 for the first Randori and the score 1 for the second Randori and so on. Now SQL shoguns might remark that it is unclear why this statement forms a running total, which would not be entirely incorrect. After all, we tell the window for the sum function how we want to sort, but we don't specify that only preceding rows are summed. We could do this explicitly, for example, by explicitly specifying in the window that we want to evaluate all preceding rows up to the current row, by including a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW:

SELECT 
     RandoriNumber
    ,Team1Score
    ,Team2Score
    ,Team3Score
    ,Team4Score
    ,Team1RunningTotal = SUM(Team1Score) OVER (ORDER BY RandoriNumber ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
    ,Team2RunningTotal = SUM(Team2Score) OVER (ORDER BY RandoriNumber ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
    ,Team3RunningTotal = SUM(Team3Score) OVER (ORDER BY RandoriNumber ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
    ,Team4RunningTotal = SUM(Team4Score) OVER (ORDER BY RandoriNumber ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
FROM #games s

But we are lucky, in this particular case exactly the same thing happens, because an unspecified ROWS/RANGE value, a BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is assumed, i.e. it is always automatically summed over the lines that are before the current line in the window. Of course, this only works if the OVER clause is used to define a window at all.

Further reading:

Ninja-Notebooks @ GitHub

Leave a Reply

Your email address will not be published. Required fields are marked *