Re: Cumulative count (running total) window fn

On Thu, Apr 29, 2010 at 13:43, Oliver Kohll - Mailing Lists
<oliver(dot)lists(at)gtwm(dot)co(dot)uk> 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 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.

Curious note - how does the non-subselect version and the subselect
version compare performance-wise?

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/