On Thu, Apr 29, 2010 at 13:43, Oliver Kohll - Mailing Lists <oliver.lists@xxxxxxxxxx> 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/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
