Re: select progressive total

On 3/21/07, brian <brian@zijn-digital.com> wrote:
>  From the "I thought this would be trivially easy" dept:
>
> I have a table holding member data for an organisation
>
> CREAT table member (
>         id      SERIAL PRIMARY KEY,
>         applied date,
> ...
>
> and i'd like to plot the growth in memberships. I'd prefer to hand off
> the data already totaled, if possible.
>
> '2006-02-01', 452
> '2006-02-17', 453
> ...
>
> It semed to me that a self join was the way to go, but that route
> produced some flaky results. Here's my latest attempt:
>
> SELECT m1.applied AS date_applied, count(m2.id) AS num_applications
>         FROM member m1, member m2 WHERE m1.applied <= m2.applied
>         GROUP BY m1.applied ORDER BY date_applied ASC;
>
> The thing is, some applications fell on the same day, so i'm not even
> sure that this approach would work. Can anyone see where i'm going wrong
> here? And how can i handle applications that occurred on the same day?
> Can this even be done in a SELECT, or should i create a function?

you can do this with a subquery:

select
  applied_date,
  count(*),
  (select count(*) from member m2 where m2.applied_date <
m1.applied_date) as total
  from member m1
  group by 1;

performance is going to suck for large tables because the subquery
will run once for every row returned by the group.

merlin