Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Jun 19 '12 at 16:18

This question came from our site for professional and enthusiast programmers.

2  
What version of SQL Server please? –  Aaron Bertrand Jun 19 '12 at 0:54
    
Also can you share the scope of the data - are we talking about 1000 rows, a million, a billion? And is it really just these two columns, or have you simplified the schema for us? –  Aaron Bertrand Jun 19 '12 at 0:59
1  
Finally, is Day a key, and are the values contiguous? –  Aaron Bertrand Jun 19 '12 at 1:15
1  
@user1465095, Where the heck are ya and why won't you answer any of the questions you been asked about your problem? –  Jeff Moden Jan 21 '13 at 0:05
1  
@Jeff unfortunately user1465095 didn't follow his question over here when it was migrated from SO, so he probably has no idea about any of the answers or comments. My thought was to chase him up on SO with a comment on some other Q or A he has, but no luck: he hasn't asked any. I think the chances are slim that we'll ever hear from him again I'm afraid. –  Jack Douglas Feb 8 '13 at 12:27

3 Answers 3

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 c INTO @d, @c;

WHILE @@FETCH_STATUS = 0
BEGIN
  SET @rt2 = @rt2 + @c;
  UPDATE #x2 SET rt = @rt2 WHERE [Day] = @d;
  FETCH 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.


demo

I've added a sqlfiddle. Results:

enter image description here


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

I've blogged more about this here:

http://www.sqlperformance.com/2012/07/t-sql-queries/running-totals

share|improve this answer
    
Aaron - I really need to make this new "Query Terminator" feature I added more obvious as to its function (clearly my "documentation", such as it is, is pathetically lacking). When you change the query terminator to something other than semi-colon for your quirky update, it works fine: sqlfiddle.com/#!6/2464b/4 This is because it runs the whole set of code as a single batch, rather than breaking them up into isolated statements. –  Jake Feasel Jun 19 '12 at 4:57
    
Similarly for cursors: sqlfiddle.com/#!6/2464b/5 –  Jake Feasel Jun 19 '12 at 4:59
    
I posted a modified version of your row_number cte. It would be really interesting if you could time that on your end. If you like just copy the code and add the timings and I will remove my answer. I have not compared to the other versions but it is a big improvement compared to the row_number version. –  Mikael Eriksson Jun 19 '12 at 5:03
    
BTW, good point on the difference between rows and range and if your order by clause has ties they will give you different results. –  Mikael Eriksson Jun 19 '12 at 5:19
    
@AaronBertrand sorry about that - was a bug with the system. –  Jake Feasel Jun 19 '12 at 14:55

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
share|improve this answer
    
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 '12 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 '12 at 0:52
3  
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. –  Aaron Bertrand Jun 19 '12 at 0:54
6  
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. –  Martin Smith Jun 19 '12 at 6:37
2  
@MartinSmith It is a VERY BIG article at sqlservercentral.com (go to Author page and find his' articles on quirck updates). –  Fabricio Araujo Jun 19 '12 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 
share|improve this answer
2  
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/… –  Michael Buen Jun 19 '12 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 '12 at 5:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.