monthly tally of new memberships

From: brian <brian(at)zijn-digital(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: monthly tally of new memberships
Date: 2007-07-18 18:29:37
Message-ID: 469E5C11.7030503@zijn-digital.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andrej Ricnik-Bay 2007-07-18 18:39:21 Re: Sylph-Searcher 1.0.0 released
Previous Message Erik Peterson 2007-07-18 18:25:29 Re: Update of table lags execution of statement by >1 minute?