Re: Running/cumulative count using windows

Hello,

I'm still reasonably new to windowing functions, having used a few since 8.4 came out. I wonder if anyone can help with this one.

I've got a table of email addresses in a CRM system similar to the following:

CREATE TABLE test(
signup_date timestamp,
email_address varchar(1000)
);
INSERT INTO test(signup_date, email_address) VALUES(now(), 'test(at)test(dot)com');
INSERT INTO test(signup_date, email_address) VALUES(now(), 'test(at)test1(dot)com');
INSERT INTO test(signup_date, email_address) VALUES(now() - '1 month'::interval, 'test(at)test2(dot)com');

I'd like a running count, or cumulative count of the number of signups per month. I'm pretty sure a window function would do it but I can't work it out.

So a plain count by month would be

SELECT date_part('year',signup_date) as year, date_part('month',signup_date) as month, count(*)
FROM test
GROUP BY year, month
ORDER BY year, month;

giving

year | month | count
------+-------+-------
2010 | 2 | 1
2010 | 3 | 2

How would you make the count a cumulative one? The output should then be

year | month | count
------+-------+-------
2010 | 2 | 1
2010 | 3 | 3

Regards
Oliver Kohll

oliver(at)agilebase(dot)co(dot)uk / +44(0)845 456 1810 / skype:okohll
www.agilebase.co.uk - software
www.gtwm.co.uk - company

In response to Oliver Kohll - Mailing Lists :
> Hello,
>
> I'm still reasonably new to windowing functions, having used a few since 8.4
> came out. I wonder if anyone can help with this one.
>
> I've got a table of email addresses in a CRM system similar to the following:
>
> CREATE TABLE test(
> signup_date timestamp,
> email_address varchar(1000)
> );
> INSERT INTO test(signup_date, email_address) VALUES(now(), 'test(at)test(dot)com');
> INSERT INTO test(signup_date, email_address) VALUES(now(), 'test(at)test1(dot)com');
> INSERT INTO test(signup_date, email_address) VALUES(now() - '1
> month'::interval, 'test(at)test2(dot)com');
>
> I'd like a running count, or cumulative count of the number of signups per
> month. I'm pretty sure a window function would do it but I can't work it out.
>
> So a plain count by month would be
>
> SELECT date_part('year',signup_date) as year, date_part('month',signup_date) as
> month, count(*)
> FROM test
> GROUP BY year, month
> ORDER BY year, month;
>
> giving
>
> year | month | count
> ------+-------+-------
> 2010 | 2 | 1
> 2010 | 3 | 2
>
> How would you make the count a cumulative one? The output should then be
>
> year | month | count
> ------+-------+-------
> 2010 | 2 | 1
> 2010 | 3 | 3
>

test=*# select * from test;
signup_date | email_address
----------------------------+----------------
2010-03-30 13:12:17.908418 | test(at)test(dot)com
2010-03-30 13:12:17.908418 | test(at)test1(dot)com
2010-02-28 13:12:17.908418 | test(at)test2(dot)com
(3 rows)

test=*# select extract (year from signup_date)::text || '/' ||
extract(month from signup_date)::text, count(email_address),
sum(count(email_address)) over (ROWS UNBOUNDED PRECEDING) from test
group by 1 order by 1;
?column? | count | sum
----------+-------+-----
2010/2 | 1 | 1
2010/3 | 2 | 3
(2 rows)

Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99