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@test.com');
INSERT INTO test(signup_date, email_address) VALUES(now(),
'test@test1.com');
INSERT INTO test(signup_date, email_address) VALUES(now() - '1 month'::interval,
'test@test2.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
