Re: Cumulative count (running total) window fn

>>
>> 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

Almost, but put me on the right track! This one is exactly what I'm looking for:

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

The ORDER BY count(email_address) did give the same results for my data but only because the count values just happen to give the same ordering as the years - I tested by changing some dates.

Many thanks all.
Oliver