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