PostgreSQL 13

Assuming a simplified table plans like the following, it can be assumed that there is at least 1 row for every month and sometimes multiple rows on the same day:

My simplified query:

SELECT TO_CHAR(plans.first_published_at, 'YYYY-MM') AS publication_date, COUNT(*)
FROM plans
WHERE plans.first_published_at IS NOT NULL
GROUP BY TO_CHAR(plans.first_published_at, 'YYYY-MM');

This gives me the following result:

But the result I would need for October is 4.

For every month, the count should be an aggregation of the current month and ALL previous months. I would appreciate any insight on how to approach this.

CodePudding user response：

I would use your query as a CTE and run a select that uses cumulative sum as a window function.

with t as
(
 SELECT TO_CHAR(plans.first_published_at, 'YYYY-MM') AS publication_date, 
        COUNT(*) AS cnt
 FROM plans
 WHERE plans.first_published_at IS NOT NULL
 GROUP BY publication_date
)
select publication_date, 
       sum(cnt) over (order by publication_date) as "count"
from t
order by publication_date desc;

Demo on DB fiddle