Re: [GENERAL] Cumulative count (running total) window fn

2010-04-30 Thread Alban Hertroys
On 29 Apr 2010, at 19:21, Oliver Kohll - Mailing Lists wrote: > The two plans (note I've been rewriting the field names for readability until > now but haven't here): > > explain analyze SELECT year, sum(c) over (order by year) > FROM ( > SELECT extract(year f

Re: [GENERAL] Cumulative count (running total) window fn

2010-04-29 Thread Oliver Kohll - Mailing Lists
> > Curious note - how does the non-subselect version and the subselect > version compare performance-wise? Magnus, On a test table with 12,000 rows there's not much in it, the subselect has a simpler plan but they both take practically the same time. The two plans (note I've been rewriting th

Re: [GENERAL] Cumulative count (running total) window fn

2010-04-29 Thread Magnus Hagander
On Thu, Apr 29, 2010 at 13:43, Oliver Kohll - Mailing Lists wrote: >> >> Aren't you looking for something along the line of: >> >> SELECT year, sum(c) over (order by year) >> FROM ( >>  SELECT extract(year from signup_date) AS year, count(email_address) AS c >>  FROM email_list >>  GROUP BY extrac

Re: [GENERAL] Cumulative count (running total) window fn

2010-04-29 Thread Oliver Kohll - Mailing Lists
>> >> Aren't you looking for something along the line of: >> >> SELECT year, sum(c) over (order by year) >> FROM ( >> SELECT extract(year from signup_date) AS year, count(email_address) AS c >> FROM email_list >> GROUP BY extract(year from signup_date) >> ) >> >> (adjust for typos, I didn't t

Re: [GENERAL] Cumulative count (running total) window fn

2010-04-29 Thread Thom Brown
On 29 April 2010 11:39, Oliver Kohll - Mailing Lists < oliver.li...@gtwm.co.uk> wrote: > > On 29 Apr 2010, at 10:01, Magnus Hagander wrote: > > > select extract(year from signup_date), > > count(email_address), > > sum(count(email_address)) over (partition by 1 order by 1 asc rows > unbounded pr

Re: [GENERAL] Cumulative count (running total) window fn

2010-04-29 Thread Oliver Kohll - Mailing Lists
On 29 Apr 2010, at 10:01, Magnus Hagander wrote: >> >> select extract(year from signup_date), >> count(email_address), >> sum(count(email_address)) over (partition by 1 order by 1 asc rows >> unbounded preceding) >> from email_list group by 1 order by 1; >> >> Does anyone have any other ideas

Re: [GENERAL] Cumulative count (running total) window fn

2010-04-29 Thread Magnus Hagander
On Thu, Apr 29, 2010 at 10:52, Oliver Kohll - Mailing Lists wrote: > Hello, > > Many thanks to andreas.kretschmer for this helpful reply about how to set up > a window function to perform a running total: > http://archives.postgresql.org/pgsql-general/2010-03/msg01122.php > > It works perfectly w

Re: [GENERAL] cumulative count

2008-12-04 Thread David Fetter
On Thu, Dec 04, 2008 at 05:53:06PM +, Carson Farmer wrote: > Hi list, > > This is my first post to pgsql, so hopefully I'm not asking something > that has been answered a thousand time before. I've looked online, and > through the archives, but I haven't found anything that answers my > q

Re: [GENERAL] cumulative count

2008-12-04 Thread Pavel Stehule
2008/12/4 Carson Farmer <[EMAIL PROTECTED]>: > Hi list, > > This is my first post to pgsql, so hopefully I'm not asking something that > has been answered a thousand time before. I've looked online, and through > the archives, but I haven't found anything that answers my question > specifically: >

Re: [GENERAL] cumulative count

2008-12-04 Thread Gerhard Heift
On Thu, Dec 04, 2008 at 07:32:59PM +0100, Harald Fuchs wrote: > In article <[EMAIL PROTECTED]>, > Carson Farmer <[EMAIL PROTECTED]> writes: > > > date | user > > --+- > > 20050201 | Bill > > 20050210 | Steve > > 20050224 |

Re: [GENERAL] cumulative count

2008-12-04 Thread Harald Fuchs
In article <[EMAIL PROTECTED]>, Carson Farmer <[EMAIL PROTECTED]> writes: > date | user > --+- > 20050201 | Bill > 20050210 | Steve > 20050224 | Sally > 20050311 | Martha > 20050316 | Ryan > 20050322 |