Skip site navigation (1) Skip section navigation (2)

Re: Cumulative count (running total) window fn

From: Magnus Hagander <magnus(at)hagander(dot)net>
To: Oliver Kohll - Mailing Lists <oliver(dot)lists(at)gtwm(dot)co(dot)uk>
Cc: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: Cumulative count (running total) window fn
Date: 2010-04-29 09:01:14
Message-ID: z2w9837222c1004290201ta8359d32g9711d94b712be282@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
On Thu, Apr 29, 2010 at 10:52, Oliver Kohll - Mailing Lists
<oliver(dot)lists(at)gtwm(dot)co(dot)uk> wrote:
> 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?

Aren't you looking for something along the line of:

SELECT year, sum(c) over (order by year)
FROM (
  SELECT extract(year from signup_date) AS year, count(email_address) AS c
  FROM email_list
  GROUP BY extract(year from signup_date)
)

(adjust for typos, I didn't test it)

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

In response to

Responses

pgsql-general by date

Next:From: Cédric VillemainDate: 2010-04-29 09:10:48
Subject: Re: Storing many big files in database- should I do it?
Previous:From: Oliver Kohll - Mailing ListsDate: 2010-04-29 08:52:55
Subject: Cumulative count (running total) window fn

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group