Re: Cumulative count (running total) window fn

[Oliver Kohll - Mailing Lists <oliver.lists@xxxxxxxxxx>]

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