monthly tally of new memberships

Поиск
Список
Период
Сортировка
От brian
Тема monthly tally of new memberships
Дата
Msg-id 469E5C11.7030503@zijn-digital.com
обсуждение исходный текст
Ответы Re: monthly tally of new memberships  (Michael Glaesemann <grzm@seespotcode.net>)
Re: monthly tally of new memberships  (Jon Sime <jsime@mediamatters.org>)
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: Erik Peterson
Дата:
Сообщение: Re: Update of table lags execution of statement by >1 minute?
Следующее
От: "Andrej Ricnik-Bay"
Дата:
Сообщение: Re: Sylph-Searcher 1.0.0 released