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?
Cheers,
Carson
