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
