How to count number of rows for date then create cumulative count?

Eikra Shithil

I would like to count first the number of row entries for every date then get the running total. This is for custom transformation in the Table Transformer Macro.

Example Data:

 

Date

4/4/2022 

4/4/2022 

4/4/2022 

4/4/2022 

4/6/2022

4/6/2022

4/6/2022

4/10/2022

 

Desired Output:

 

Date          | Count | Running total

4/4/2022   | 4         | 4

4/6/2022   | 3         | 7

4/10/2022 | 1         | 8

 

 

I have tried: 

SELECT 'Date',

COUNT(*) AS 'Count',

SUM('Count') OVER (ORDER BY 'Date') AS 'Running total'

FROM T*

GROUP BY 'Date'

ORDER BY 'Date';

 

and various other variations of this code but it does not work. I can successfully get the count but not running total. 

Natalie Paramonova _Stiltsoft_

Hi @Eikra Shithil ,

We can suggest two available options for the case for you to choose from.

Option 1. Pivot Table + Table Transformer

Here is the structure for you to implement:

If your tags are correct and you are on Confluence Cloud, please use our Table Toolbox to nest macros.

Here the original table is reused via the Table Excerpt/Table Excerpt Include macros, so you will have the same data for the upper and lower Pivot Table macros.

Here are the settings for the upper Pivot Table macro:

Also check the “Hide totals” option, we don’t need it for the case.

And here are the settings for the lower Pivot Table macro:

The Data Source tab looks the same, but for the Options tab we additionally check the “Cumulative count” option.

Now we move to the Table Transformer macro and join our tables with the help of the following SQL query:

SELECT T1.'Date', T1.'Count',
T2.'Count' AS 'Running totals' FROM T1
OUTER JOIN T* ON T1.'Date' = T*.'Date'

Option 2. Table Transformer macro

Here is the screenshot of the second macro structure:

Here we transform the original table and then reuse the transformed table once more for further transformation – please be careful to place your Table Excerpt/Table Excerpt Include macros in the right places and don’t forget about the Table Toolbox macro if you are on Cloud.

Here is the SQL query for the upper internal Table Transformer macro:

SELECT 'Date',
COUNT('Date') AS 'Count'
FROM T*
GROUP BY 'Date'
ORDER BY 'Date'

Here we count rows as you may see. And don’t forget to set an appropriate Date format inside the Table Transformer macro – “m/d/yy” for your example.

Now we move to the lower internal Table Transformer macro and calculate the running totals:

SELECT 'Date',
SUM (TT2.'Count') AS 'Running totals'
FROM T1 AS TT1
JOIN T1 AS TT2 on TT1.'Date' >= TT2.'Date'
GROUP BY TT1.'Date'
ORDER BY TT1.'Date'

And don’t forget about the Date format again.

Now we go to the external Table Transformer macro and look up our transformed tables by the “Date” column:

Hope this helps in your case.