T-SQL cumulative count or cumulative sum or running total or running sum

I hope this long title will help to find this solution faster.
Finally, in December 2019 I had time to work on SQL cumulative total.
Let’s start with an example:

 

 

 

 

 

 

 

 

 

WITH STORE_COUNTS AS (
       SELECT ROW_NUMBER() OVER (ORDER BY CDCO) [RowNum],CDCO, COUNT(*) [COUNT]
       FROM F1721 CT WHERE. . . GROUP BY CDCO
)
SELECT CT1.[RowNum], CT1.CDCO, CT1.[COUNT], SUM(CT2.[COUNT]) [Cumulative]
FROM STORE_COUNTS CT1 INNER JOIN STORE_COUNTS CT2 ON CT2.[RowNum] <= CT1.[RowNum]
GROUP BY CT1.[RowNum], CT1.CDCO, CT1.[COUNT]

 

First we store grouped company counts in a temp table STORE_COUNTS using WITH construct.  STORE_COUNTS table contains contract record count by company.   That is, how many contract rows each company has.  Company code is stored in CDCO.   We also include a [RowNum] field to serve later as a key.

Now we can refer to this temp table using name STORE_COUNTS ANYTHING.  Please note a space between STORE_COUNTS and ANYTHING.  In the example above we are linking STORE_COUNTS CT1 and STORE_COUNTS CT2.

Now we are ready to output running sums.

To output running sum using group counts we will join 2 instances in the same temp table STORE_COUNTS joining them rather creatively:

CT2.[RowNum] <= CT1.[RowNum]

It means join all rows from second instance of the table only with rows of the first instance where row number is higher or the same. Resulting table will have one row for RowNum 1, two rows for RowNum 2, three for RowNum 3 etc.

All we need to do is to SUM row counts and GROUP BY the company (CDCO) again.   

Output will be similar to the results below:

 

Row Number CDCO COUNT Cumulative
1 00300 233,683 233,683
2 00310 22,333 256,016
3 00330 367 256,383
4 00332 859 257,242
5 00333 68 257,310
6 00334 214 257,524
7 00335 378 257,902
8 00336 45 257,947
9 00337 7 257,954
10 00351 8,288 266,242
11 00352 2,012 268,254
12 00353 1,608 269,862
13 00354 2,017 271,879
14 00355 554 272,433

As you can see, we now have company row counts and cumulative or running totals.   Perfect!

(Visited 1,044 times, 1 visits today)

Be the first to comment

Your question, correction or clarification Ваш вопрос, поправка или уточнение