Re: Cumulative count (running total) window fn

On 29 April 2010 11:39, Oliver Kohll - Mailing Lists <
oliver(dot)lists(at)gtwm(dot)co(dot)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 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
>

Like this?:

SELECT extract(year from signup_date), count(email_address),
sum(count(email_address)) OVER (ORDER BY count(email_address)) FROM
email_list GROUP BY 1 ORDER BY 1;

Thom