From: | Oliver Kohll - Mailing Lists <oliver(dot)lists(at)gtwm(dot)co(dot)uk> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Running/cumulative count using windows |
Date: | 2010-03-30 11:06:16 |
Message-ID: | DF46540C-2F08-40F0-BD8D-492E7C1461C2@gtwm.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | A. Kretschmer | 2010-03-30 11:26:29 | Re: Running/cumulative count using windows |
Previous Message | dipti shah | 2010-03-30 11:05:27 | Get the list of permissions on schema for current user |