Cumulative count (running total) window fn

From: Oliver Kohll - Mailing Lists <oliver(dot)lists(at)gtwm(dot)co(dot)uk>
To: pgsql-general(at)postgresql(dot)org
Subject: Cumulative count (running total) window fn
Date: 2010-04-29 08:52:55
Message-ID: 70051B0A-F2EB-4C14-A614-09092B53F013@gtwm.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

Many thanks to andreas.kretschmer for this helpful reply about how to set up a window function to perform a running total:
http://archives.postgresql.org/pgsql-general/2010-03/msg01122.php

It works perfectly with the simple test data but I've just got back to work, tried implementing it on my live data and the results are slightly different. My query is almost exactly the same - I've simplified by grouping by year only rather than year and month:

select extract(year from signup_date),
count(email_address),
sum(count(email_address)) over (rows unbounded preceding)
from email_list group by 1 order by 1;

date_part | count | sum
-----------+-------+------
2007 | 501 | 1374
2008 | 491 | 491
2009 | 382 | 873
2010 | 66 | 1440
(4 rows)

What I'm looking for is
date_part | count | sum
-----------+-------+------
2007 | 501 | 501
2008 | 491 | 992
2009 | 382 | 1374
2010 | 66 | 1440

It seems to be adding up the counts but not in the right order.

I've also tried an explicit ORDER BY inside the partition with no difference:

select extract(year from signup_date),
count(email_address),
sum(count(email_address)) over (partition by 1 order by 1 asc rows unbounded preceding)
from email_list group by 1 order by 1;

Does anyone have any other ideas?

Regards
Oliver Kohll

oliver(at)agilebase(dot)co(dot)uk / +44(0)7814 828608 / skype:okohll
www.agilebase.co.uk - software
www.gtwm.co.uk - company

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Magnus Hagander 2010-04-29 09:01:14 Re: Cumulative count (running total) window fn
Previous Message Piotr Kublicki 2010-04-29 08:40:59 Re: Start-up script for few clusters: just add water?