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:
As you can see, we now have company row counts and cumulative or running totals. Perfect!