Re: monthly tally of new memberships

On Jul 18, 2007, at 13:29 , brian wrote:

> This returns the new memberships for each day, ignoring days
> without any. What i'd like to do though, is to select only the 1st
> of each month, summing the new memberships or that month, eg:
>
> month | applications
> 2006-05-01 | 57
> 2006-06-01 | 36
> 2006-07-01 | 72

Try something like this:

SELECT date_trunc('month', applied)::date AS date_applied
, count(id) AS applications
FROM member
GROUP BY applied
ORDER BY date_applied ASC;

Note I remove the WHERE applied = applied, as this is just identity.

Michael Glaesemann
grzm seespotcode net