Re: monthly tally of new memberships

Michael Glaesemann wrote:
>
> 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.
>

Thanks, but that isn't it. I've tried that exact query, actually. The
problem with that is it doesn't give me one row for the entire month.
Instead, i get one row for each day there was a new membership, only the
date_applied column has been changed to the 1st of that particular
month. eg:

  2006-02-01   |            1
  2006-02-01   |            1
  2006-02-01   |            7
  2006-03-01   |            1
  2006-03-01   |            3
  2006-03-01   |            1
  2006-03-01   |            3
  2006-03-01   |            1
  2006-03-01   |            2

What i'd like to be able to do is to count all of the new member IDs
that have been inserted during a particular month and return that sum
along with the date for the 1st (ie '2006-03-22').

The thing is, i feel certain that i've done something very similar before.

brian