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;
date_applied | applications
2006-05-21 | 1
2006-05-22 | 1
2006-05-23 | 2
2006-05-24 | 14
2006-05-25 | 5
etc.
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
etc.
I've been fiddling with this since yesterday and am getting no closer,
it seems. I know how to do this if i pass in a particular month to
select from but not an aggregate for the entire month. Nothing i've
tried is working but this seems as if it should be quite simple.
I'll bet it's obvious, isn't it? :-\
brian
