Re: Cumulative count (running total) window fn

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?
>
> 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 test it)

Yes that does work thanks, if you give the subquery a name. I'd still like to know if it's possible to do with a window function rather than a subquery.

Oliver Kohll