PostgreSQL: Row count over time

Question

I have a simple mySQL sql-script, which outputs me the row count over time for a specific table (based on a datetime field in the table)

SELECT concat('M-', s.label)
     , s.cnt
     , @tot := @tot + s.cnt  AS running_subtotal
  FROM ( SELECT DATE_FORMAT(t.created,'%y-%m') AS `label`
              , COUNT(t.id) AS cnt
           FROM `templates` t
          GROUP BY `label`
          ORDER BY `label`
       ) s
 CROSS
  JOIN ( SELECT @tot := 0 ) i

Now I want to migrate this to PostgreSQL, but have no idea how to migrate the variables to pg-based syntax.

The inner statement is, of course, no problem:

SELECT TO_CHAR(t.created,'YYYY-MM') AS label
              , COUNT(t.id) AS cnt
           FROM templates t
          GROUP BY label
          ORDER BY label

Anyone here, who can help me with the variable-part?

Here's a simple table with data:

create TABLE "templates" (
    "id" bigserial,
    "title" varchar(2048) default NULL::character varying,
    "created" timestamp,
    PRIMARY KEY ("id")
);

insert into templates(title, created) values('test', '2011-03-01');
insert into templates(title, created) values('test 2', '2011-03-02');
insert into templates(title, created) values('test 3', '2011-03-03');
insert into templates(title, created) values('test 4', '2011-03-04');
insert into templates(title, created) values('test 5', '2011-03-05');
insert into templates(title, created) values('test 6', '2011-04-01');
insert into templates(title, created) values('test 7', '2011-04-02');
insert into templates(title, created) values('test 8', '2011-04-03');
insert into templates(title, created) values('test 9', '2011-04-04');
insert into templates(title, created) values('test 10', '2011-04-05');
… // 300 more for 2011-05

the example output of this query (based on records with the "created"-column) is:

M-11-03:   5   5
M-11-04:   5  10 (5 + 5)
M-11-05: 300 310 (5 + 5 + 300)

(This is a spin off of Table statistics (aka row count) over time)

Answer 1

This works:

select month, hm, sum(hm) over(order by month)
from(
select to_char(created, 'M-YYYY-MM') as month, count(*) as hm
from templates
group by 1
) x
order by month

http://www.sqlfiddle.com/#!15/eb08a/14