# Running total with count?

As the title suggests I need some help getting a running total in T-SQL. The problem is that the sum I need to do is the sum of a count:

sum(count (distinct (customers)))

Say if I ran the count alone, the result would be:

Day | CountCustomers
----------------------
5/1  |      1
5/2  |      0
5/3  |      5

I need output with the sum to be :

Day | RunningTotalCustomers
----------------------
5/1  |      1
5/2  |      1
5/3  |      6

I've done running totals before using the coalesce method, but never with a count. I'm not sure how to do it now that I have the count.

• What version of SQL Server please? Can you share the scope of the data - are we talking about 1000 rows, a million, a billion? Is it really just these two columns, or have you simplified the schema for us? Finally, is Day a key, and are the values contiguous? Jun 19, 2012 at 0:54
• I made a comprehensive blog about running total (Quirky update vs Hybrid Recursive CTE vs Cursor): ienablemuch.com/2012/05/… I didn't include the running total that uses pure set-based approach, the performance is nothing to be desired: sqlblog.com/blogs/adam_machanic/archive/2006/07/12/… Jun 19, 2012 at 4:16

Here are a few methods you can compare. First let's set up a table with some dummy data. I'm populating this with a bunch of random data from sys.all_columns. Well, it's kind of random - I'm ensuring that the dates are contiguous (which is really only important for one of the answers).

CREATE TABLE dbo.Hits(Day SMALLDATETIME, CustomerID INT);

CREATE CLUSTERED INDEX x ON dbo.Hits([Day]);

INSERT dbo.Hits SELECT TOP (5000) DATEADD(DAY, r, '20120501'),
COALESCE(ASCII(SUBSTRING(name, s, 1)), 86)
FROM (SELECT name, r = ROW_NUMBER() OVER (ORDER BY name)/10,
s = CONVERT(INT, RIGHT(CONVERT(VARCHAR(20), [object_id]), 1))
FROM sys.all_columns) AS x;

SELECT
Earliest_Day   = MIN([Day]),
Latest_Day     = MAX([Day]),
Unique_Days    = DATEDIFF(DAY, MIN([Day]), MAX([Day])) + 1,
Total_Rows     = COUNT(*)
FROM dbo.Hits;

Results:

Earliest_Day         Latest_Day           Unique_Days  Total_Days
-------------------  -------------------  -----------  ----------
2012-05-01 00:00:00  2013-09-13 00:00:00  501          5000

The data looks like this (5000 rows) - but will look slightly different on your system depending on version and build #:

Day                  CustomerID
-------------------  ---
2012-05-01 00:00:00  95
2012-05-01 00:00:00  97
2012-05-01 00:00:00  97
2012-05-01 00:00:00  117
2012-05-01 00:00:00  100
...
2012-05-02 00:00:00  110
2012-05-02 00:00:00  110
2012-05-02 00:00:00  95
...

And the running totals results should look like this (501 rows):

Day                  c   rt
-------------------  --  --
2012-05-01 00:00:00  6   6
2012-05-02 00:00:00  5   11
2012-05-03 00:00:00  4   15
2012-05-04 00:00:00  7   22
2012-05-05 00:00:00  6   28
...

So the methods I am going to compare are:

• "self-join" - the set-based purist approach
• "recursive CTE with dates" - this relies on contiguous dates (no gaps)
• "recursive CTE with row_number" - similar to above but slower, relying on ROW_NUMBER
• "recursive CTE with #temp table" - stolen from Mikael's answer as suggested
• "quirky update" which, while unsupported and not promising defined behavior, seems to be quite popular
• "cursor"
• SQL Server 2012 using new windowing functionality

## self-join

This is the way people will tell you to do it when they're warning you to stay away from cursors, because "set-based is always faster." In some recent experiments I've found that the cursor out-paces this solution.

;WITH g AS
(
SELECT [Day], c = COUNT(DISTINCT CustomerID)
FROM dbo.Hits
GROUP BY [Day]
)
SELECT g.[Day], g.c, rt = SUM(g2.c)
FROM g INNER JOIN g AS g2
ON g.[Day] >= g2.[Day]
GROUP BY g.[Day], g.c
ORDER BY g.[Day];

## recursive cte with dates

Reminder - this relies on contiguous dates (no gaps), up to 10000 levels of recursion, and that you know the start date of the range you're interested (to set the anchor). You could set the anchor dynamically using a subquery, of course, but I wanted to keep things simple.

;WITH g AS
(
SELECT [Day], c = COUNT(DISTINCT CustomerID)
FROM dbo.Hits
GROUP BY [Day]
), x AS
(
SELECT [Day], c, rt = c
FROM g
WHERE [Day] = '20120501'
UNION ALL
SELECT g.[Day], g.c, x.rt + g.c
FROM x INNER JOIN g
ON g.[Day] = DATEADD(DAY, 1, x.[Day])
)
SELECT [Day], c, rt
FROM x
ORDER BY [Day]
OPTION (MAXRECURSION 10000);

## recursive cte with row_number

Row_number calculation is slightly expensive here. Again this supports max level of recursion of 10000, but you don't need to assign the anchor.

;WITH g AS
(
SELECT [Day], rn = ROW_NUMBER() OVER (ORDER BY DAY),
c = COUNT(DISTINCT CustomerID)
FROM dbo.Hits
GROUP BY [Day]
), x AS
(
SELECT [Day], rn, c, rt = c
FROM g
WHERE rn = 1
UNION ALL
SELECT g.[Day], g.rn, g.c, x.rt + g.c
FROM x INNER JOIN g
ON g.rn = x.rn + 1
)
SELECT [Day], c, rt
FROM x
ORDER BY [Day]
OPTION (MAXRECURSION 10000);

## recursive cte with temp table

Stealing from Mikael's answer, as suggested, to include this in the tests.

CREATE TABLE #Hits
(
rn INT PRIMARY KEY,
c INT,
[Day] SMALLDATETIME
);

INSERT INTO #Hits (rn, c, Day)
SELECT ROW_NUMBER() OVER (ORDER BY DAY),
COUNT(DISTINCT CustomerID),
[Day]
FROM dbo.Hits
GROUP BY [Day];

WITH x AS
(
SELECT [Day], rn, c, rt = c
FROM #Hits as c
WHERE rn = 1
UNION ALL
SELECT g.[Day], g.rn, g.c, x.rt + g.c
FROM x INNER JOIN #Hits as g
ON g.rn = x.rn + 1
)
SELECT [Day], c, rt
FROM x
ORDER BY [Day]
OPTION (MAXRECURSION 10000);

DROP TABLE #Hits;

## quirky update

Again I am only including this for completeness; I personally wouldn't rely on this solution since, as I mentioned on another answer, this method is not guaranteed to work at all, and may completely break in a future version of SQL Server. (I'm doing my best to coerce SQL Server into obeying the order I want, using a hint for the index choice.)

CREATE TABLE #x([Day] SMALLDATETIME, c INT, rt INT);
CREATE UNIQUE CLUSTERED INDEX x ON #x([Day]);

INSERT #x([Day], c)
SELECT [Day], c = COUNT(DISTINCT CustomerID)
FROM dbo.Hits
GROUP BY [Day]
ORDER BY [Day];

DECLARE @rt1 INT;
SET @rt1 = 0;

UPDATE #x
SET @rt1 = rt = @rt1 + c
FROM #x WITH (INDEX = x);

SELECT [Day], c, rt FROM #x ORDER BY [Day];

DROP TABLE #x;

## cursor

"Beware, there be cursors here! Cursors are evil! You should avoid cursors at all costs!" No, that's not me talking, it's just stuff I hear a lot. Contrary to popular opinion, there are some cases where cursors are appropriate.

CREATE TABLE #x2([Day] SMALLDATETIME, c INT, rt INT);
CREATE UNIQUE CLUSTERED INDEX x ON #x2([Day]);

INSERT #x2([Day], c)
SELECT [Day], COUNT(DISTINCT CustomerID)
FROM dbo.Hits
GROUP BY [Day]
ORDER BY [Day];

DECLARE @rt2 INT, @d SMALLDATETIME, @c INT;
SET @rt2 = 0;

DECLARE c CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR SELECT [Day], c FROM #x2 ORDER BY [Day];

OPEN c;

FETCH NEXT FROM c INTO @d, @c;

WHILE @@FETCH_STATUS = 0
BEGIN
SET @rt2 = @rt2 + @c;
UPDATE #x2 SET rt = @rt2 WHERE [Day] = @d;
FETCH NEXT FROM c INTO @d, @c;
END

SELECT [Day], c, rt FROM #x2 ORDER BY [Day];

DROP TABLE #x2;

## SQL Server 2012

If you are on the most recent version of SQL Server, enhancements to windowing functionality allows us to easily calculate running totals without the exponential cost of self-joining (the SUM is calculated in one pass), the complexity of the CTEs (including the requirement of contiguous rows for the better performing CTE), the unsupported quirky update and the forbidden cursor. Just be wary of the difference between using RANGE and ROWS, or not specifying at all - only ROWS avoids an on-disk spool, which will hamper performance significantly otherwise.

;WITH g AS
(
SELECT [Day], c = COUNT(DISTINCT CustomerID)
FROM dbo.Hits
GROUP BY [Day]
)
SELECT g.[Day], c,
rt = SUM(c) OVER (ORDER BY [Day] ROWS UNBOUNDED PRECEDING)
FROM g
ORDER BY g.[Day];

## performance comparisons

I took each approach and wrapped it a batch using the following:

SELECT SYSUTCDATETIME();
GO
DBCC DROPCLEANBUFFERS;DBCC FREEPROCCACHE;
-- query here
GO 10
SELECT SYSUTCDATETIME();

Here are the results of the total duration, in milliseconds (remember this includes the DBCC commands each time as well):

method                          run 1     run 2
-----------------------------   --------  --------
self-join                        1296 ms   1357 ms -- "supported" non-SQL 2012 winner
recursive cte with dates         1655 ms   1516 ms
recursive cte with row_number   19747 ms  19630 ms
recursive cte with #temp table   1624 ms   1329 ms
quirky update                     880 ms   1030 ms -- non-SQL 2012 winner
cursor                           1962 ms   1850 ms
SQL Server 2012                   847 ms    917 ms -- winner if SQL 2012 available

And I did it again without the DBCC commands:

method                          run 1     run 2
-----------------------------   --------  --------
self-join                        1272 ms   1309 ms -- "supported" non-SQL 2012 winner
recursive cte with dates         1247 ms   1593 ms
recursive cte with row_number   18646 ms  18803 ms
recursive cte with #temp table   1340 ms   1564 ms
quirky update                    1024 ms   1116 ms -- non-SQL 2012 winner
cursor                           1969 ms   1835 ms
SQL Server 2012                   600 ms    569 ms -- winner if SQL 2012 available

Removing both the DBCC and loops, just measuring one raw iteration:

method                          run 1     run 2
-----------------------------   --------  --------
self-join                         313 ms    242 ms
recursive cte with dates          217 ms    217 ms
recursive cte with row_number    2114 ms   1976 ms
recursive cte with #temp table     83 ms    116 ms -- "supported" non-SQL 2012 winner
quirky update                      86 ms     85 ms -- non-SQL 2012 winner
cursor                           1060 ms    983 ms
SQL Server 2012                    68 ms     40 ms -- winner if SQL 2012 available

Finally, I multiplied the row count in the source table by 10 (changing top to 50000 and adding another table as a cross join). The results of this, one single iteration with no DBCC commands (simply in the interests of time):

method                           run 1      run 2
-----------------------------    --------   --------
self-join                         2401 ms    2520 ms
recursive cte with dates           442 ms     473 ms
recursive cte with row_number   144548 ms  147716 ms
recursive cte with #temp table     245 ms     236 ms -- "supported" non-SQL 2012 winner
quirky update                      150 ms     148 ms -- non-SQL 2012 winner
cursor                            1453 ms    1395 ms
SQL Server 2012                    131 ms     133 ms -- winner

I only measured duration - I'll leave it as an exercise to the reader to compare these approaches on their data, comparing other metrics that may be important (or may vary with their schema/data). Before drawing any conclusions from this answer, it'll be up to you to test it against your data and your schema... these results will almost certainly change as the row counts get higher.

## conclusion

In my tests, the choice would be:

1. SQL Server 2012 method, if I have SQL Server 2012 available.
2. If SQL Server 2012 is not available, and my dates are contiguous, I would go with the recursive cte with dates method.
3. If neither 1. nor 2. are applicable, I would go with the self-join over the quirky update, even though the performance was close, just because the behavior is documented and guaranteed. I'm less worried about future compatibility because hopefully if the quirky update breaks it will be after I've already converted all my code to 1. :-)

But again, you should test these against your schema and data. Since this was a contrived test with relatively low row counts, it may as well be a fart in the wind. I've done other tests with different schema and row counts, and the performance heuristics were quite different... which is why I asked so many follow-up questions to your original question.

UPDATE

Best approaches for running totals – updated for SQL Server 2012

This is, apparently, the optimal solution

DECLARE @dailyCustomers TABLE (day smalldatetime, CountCustomers int, RunningTotal int)

DECLARE @RunningTotal int

SET @RunningTotal = 0

INSERT INTO @dailyCustomers
SELECT day, CountCustomers, null
FROM Sales
ORDER BY day

UPDATE @dailyCustomers
SET @RunningTotal = RunningTotal = @RunningTotal + CountCustomers
FROM @dailyCustomers

SELECT * FROM @dailyCustomers
• Any ideas without implementing a temp table ( my proc is already forcing values through several temp tables by necessity, so I am trying to find a way to avoid using another temp table)? If not, I will use this method. I think it will work
– user1465095
Jun 19, 2012 at 0:47
• It can also be done with a self join, or a nested subquery but these options don't perform nearly as well. Also it's likely you'll be hitting tempdb anyway with these alternatives with some spooling or worktables.
– M_M
Jun 19, 2012 at 0:52
• Just be aware that this "quirky update" method is not guaranteed to work - this syntax is unsupported and its behavior is undefined, and it can break in a future version, hot fix or service pack. So while yes it's faster than some supported alternatives, that comes at a potential future compatibility cost. Jun 19, 2012 at 0:54
• There are lots of caveats to this approach which Jeff Moden has written up somewhere. You should have a clustered index on day for example. Jun 19, 2012 at 6:37
• @MartinSmith It is a VERY BIG article at sqlservercentral.com (go to Author page and find his' articles on quirck updates). Jun 19, 2012 at 20:20

Just another way, costly, but version independent. It doesn't use temp tables or variables.

select T.dday, T.CustomersByDay +
(select count(A.customer) from NewCustomersByDate A
where A.dday < T.dday) as TotalCustomerTillNow
from (select dday, count(customer) as CustomersByDay
from NewCustomersByDate group by dday) T
• That is not good, that is very slow. Even you just have a 100 rows, it will do a ping-pong read between tables at 5,050 times. 200 rows, is 20,100 times. With 1,000 rows only, it jumps exponentially to 500,500 reads sqlblog.com/blogs/adam_machanic/archive/2006/07/12/… Jun 19, 2012 at 4:34
• I saw the link to your blog after posting this, now i see this is a very bad idea, thanks!
– Jcis
Jun 19, 2012 at 5:01