On 29 April 2010 11:39, Oliver Kohll - Mailing Lists < oliver.li...@gtwm.co.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
