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(), 't...@test.com');
INSERT INTO test(signup_date, email_address) VALUES(now(), 't...@test1.com');
INSERT INTO test(signup_date, email_address) VALUES(now() - '1
month'::interval, 't...@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
oli...@agilebase.co.uk / +44(0)845 456 1810 / skype:okohll
www.agilebase.co.uk - software
www.gtwm.co.uk - company
