Re: monthly tally of new memberships

brian wrote:
> I'm trying to create a select statement that will show me the number of
> new memberships or an organisation by date (first of each month). The
> member table has a date column to reflect when the member was inserted.
> So far, i've gotten as far as:
>
> SELECT applied AS date_applied, count(id) AS applications
> FROM member WHERE applied = applied
> GROUP BY applied
> ORDER BY date_applied ASC;

Try this instead:

select to_char(applied, 'yyyy-mm') as month_applied,
count(id) as applications
from member
group by to_char(applied, 'yyyy-mm')
order by 1 asc;

Your WHERE condition seems superfluous, unless you're using that to
remove any records where applied is NULL. If that's the case, it would
be much more readable and intuitive to use "where applied is not null".

-Jon

--
Senior Systems Developer
Media Matters for America
http://mediamatters.org/