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