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(), '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
> 

test=*# select * from test;
        signup_date         | email_address
----------------------------+----------------
 2010-03-30 13:12:17.908418 | t...@test.com
 2010-03-30 13:12:17.908418 | t...@test1.com
 2010-02-28 13:12:17.908418 | t...@test2.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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Reply via email to