Re: monthly tally of new memberships

brian wrote:
> 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:

Hum, you should be grouping by date_applied (also known as "group by 1"
because you can't use column aliases in GROUP BY)

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.