2008/12/4 Carson Farmer <carson.farmer@xxxxxxxxx>: > Hi list, > > This is my first post to pgsql, so hopefully I'm not asking something that > has been answered a thousand time before. I've looked online, and through > the archives, but I haven't found anything that answers my question > specifically: > > Say I have a table like this: > > date | user > ------------------+--------------------- > 20050201 | Bill > 20050210 | Steve > 20050224 | Sally > 20050311 | Martha > 20050316 | Ryan > 20050322 | Phil > 20050330 | William > 20050415 | Mary > 20050428 | Susan > 20050503 | Jim > > and I want to run a query that returns a *count* of the number of users > *each month*, ordered by year and *month*, with an additional column that is > a *running total of the count*, as in: > > year | month | count | run_count > -------------------+----------------+----------------+----------------- > 2005 | 02 | 3 | 3 2005 > | 03 | 4 | 7 > 2005 | 04 | 2 | 9 > 2005 | 05 | 1 | 10 > > I can get almost everything I want with: > > SELECT a.year, a.month, a.count, a.month_name, sum(b.count) AS total > FROM (SELECT EXTRACT(year from added_date) AS year, > EXTRACT(month FROM added_date) AS month, > TO_CHAR(added_date, 'Month') AS month_name, > COUNT(*) AS count FROM users_table GROUP BY 1, 2, 3) AS a, > (SELECT EXTRACT(year FROM added_date) AS year, > EXTRACT(month FROM added_date) AS month, > TO_CHAR(added_date, 'Month') AS month_name, > COUNT(*) AS count > FROM users_table GROUP BY 1, 2, 3) AS b > WHERE a.year >= b.year AND a.month >= b.month > GROUP BY 1, 2, 3, 4 > ORDER BY a.year, a.month asc; > > but I can't quite figure out the running total of the count. The above > example works right up to the end of the first year, then the values no > longer make sense. My guess is it's something to do with my WHERE clause, > but I can't think of a better way to do things. > > Any ideas? > hate selfjoins. It is really slow for any bigger datasets. Write SRF function (stored function that returns table). Regards Pavel Stehule > Cheers, > > Carson > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
