Re: cumulative count - Mailing list pgsql-general

2008/12/4 Carson Farmer <carson.farmer@gmail.com>:
> 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>