Merlin Moncure wrote:
On 3/21/07, brian <brian@xxxxxxxxxxxxxxxx> 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;
Almost. The total column is less than the correct amount by whatever count returns.
performance is going to suck for large tables because the subquery will run once for every row returned by the group.
Yeah, i realise. In the end, i think i'll do the totalling outside of Postgres. When plotting the data, i'll be sending it as an assoc. array, like: '2006-05-27'=>3049 so i need to do some post-processing in any case.
b
