Обсуждение: monthly tally of new memberships
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
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.
Michael Glaesemann
grzm seespotcode net
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/
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
brian wrote:
> 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:
Hum, you should be grouping by date_applied (also known as "group by 1"
because you can't use column aliases in GROUP BY)
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera wrote:
> brian wrote:
>
>>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:
>
>
> Hum, you should be grouping by date_applied (also known as "group by 1"
> because you can't use column aliases in GROUP BY)
>
Right, that works, also.
I compared this to Jon Sime's suggestion:
test=# EXPLAIN ANALYZE SELECT date_trunc('month', applied)::date AS
date_applied, count(id) AS applications FROM member GROUP BY 1 ORDER BY
date_applied ASC;
QUERY PLAN
-------------------------------------------------------
Sort (cost=140.76..141.26 rows=200 width=8) (actual time=17.590..17.622
rows=18 loops=1)
Sort Key: (date_trunc('month'::text, (applied)::timestamp with time
zone))::date
-> HashAggregate (cost=129.12..133.12 rows=200 width=8) (actual
time=17.478..17.523 rows=18 loops=1)
-> Seq Scan on member (cost=0.00..123.76 rows=1072 width=8)
(actual time=0.035..10.684 rows=1072 loops=1)
Total runtime: 17.733 ms
(5 rows)
test=# EXPLAIN ANALYZE 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;
QUERY PLAN
-----------------------------------------------------
Sort (cost=137.58..138.08 rows=200 width=8) (actual
time=13.415..13.458 rows=18 loops=1)
Sort Key: to_char((applied)::timestamp with time zone, 'yyyy-mm'::text)
-> HashAggregate (cost=126.44..129.94 rows=200 width=8) (actual
time=13.273..13.314 rows=18 loops=1)
-> Seq Scan on member (cost=0.00..121.08 rows=1072 width=8)
(actual time=0.042..10.525 rows=1072 loops=1)
Total runtime: 13.564 ms
(5 rows)
But, getting back to your comment, i see that this (grouping by the
alias) also works:
test=# EXPLAIN ANALYZE SELECT to_char(applied, 'yyyy-mm') AS
month_applied, count(id) AS applications FROM member GROUP BY
month_applied ORDER BY 1 ASC;
QUERY PLAN
-----------------------------------------------------
Sort (cost=137.58..138.08 rows=200 width=8) (actual
time=44.329..44.363 rows=18 loops=1)
Sort Key: to_char((applied)::timestamp with time zone, 'yyyy-mm'::text)
-> HashAggregate (cost=126.44..129.94 rows=200 width=8) (actual
time=44.190..44.229 rows=18 loops=1)
-> Seq Scan on member (cost=0.00..121.08 rows=1072 width=8)
(actual time=0.042..41.242 rows=1072 loops=1)
Total runtime: 44.477 ms
(5 rows)
But it seems to take longer at the cost of keeping the query tidy.
brian