Create running totals with data.

This is a fairly common requirement when reporting on data. The data analyst will typically extract data using sql and then use the SUM formula within Excel (with the aid of fixed and relative references, that are copied down). For example, supposing we are looking at a table that records number of starters by dates. We could have data like the following:

"record_date";"yr1starter"

"2004-01-01";"820"

"2004-01-02";"1891"

"2004-01-03";"1667"

"2004-01-04";"1607"

"2004-01-05";"1542"

We copy this into a spreadsheet (normally by using text-to-columns so the data is in separate columns). In order to get a running balance we can then use the following formula: =SUM(B$2:B2) for cell C2. We then copy down, so for instance the formula in cell C3 becomes: =SUM(B$2:B3) It's not the only way to do this in Excel - you could always add the subtotal above to the current record field, but that runs the danger of a gap in the figures. The attached graphic shows what the cumulative formula list will look like

Excel will normally display the calculations for example:

yr1starters cumulative total

820 820

1891 2711

1667 4378

1607 5985

1542 7527

However, it is possible to create cumulative/running totals in SQL (via an inner query, normally called a sub-query). I will first show this using MySql syntax:

SET @runtotal:=0;

SELECT qry1.r_date, qry1.day_starts,

(@runtotal := @runtotal + qry1.day_starts) AS runningtotal

FROM

(SELECT

record_date AS r_date, yr1starts AS day_starts

FROM startstops

WHERE record_date between '2004-01-01' AND '2004-01-31'

ORDER BY record_date) AS qry1

The key is to set the initial variable @runtototal to 0, so it can then total up the running total (the ORDER BY clause at the end of the select statement is also important). Incidentally, this query (and other ways to achieve it using Mysql) can be seen at StackOverflow. It is actually a bit more difficult to achieve this using sql*server. One way is to use a cursor, which I feel is OTT for this. Another way is to use a cross-join, and so join the table to itself. Performance can be an issue here if the data set is large (helped possibly by indexes)

SELECT a.record_date, a.yr1starter, SUM(b.yr1starter) AS running_total

FROM dbo.startstops a

CROSS JOIN dbo.startstops b

WHERE (b.record_date <= a.record_date)

AND a.record_date between '2004-01-01' AND '2004-01-31' AND b.record_date between '2004-01-01' AND '2004-01-31'

GROUP BY a.record_date,a.yr1starter ORDER BY a.record_date,a.yr1starter

You can read about different ways of calculating running totals in sql*server (including the usage of cursors) at the sqlteam link.