Search Postgresql Archives

Re: Cumulative count (running total) window fn

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux