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