Date: Sat, 11 Oct 2003 12:49:00 -0700
From: elein <elein@varlena.com>
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: Running tally
Message-ID: <20031011124900.G6483@cookie.varlena.com>
Mail-Followup-To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>,
	pgsql-sql@postgresql.org
References: <3F7E9956.30804@familyhealth.com.au>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <3F7E9956.30804@familyhealth.com.au>
User-Agent: Mutt/1.3.22.1i

You can use plpythonu (or tcl or C or R) to do running
sums.  For plpythonu, you must initialize the SD[]
by calling it first with the proper argument.

create or replace function runsum(int,int)
returns int as
'
  if args[0] == 1:
       SD["currval"] = 0
       return SD["currval"]
  else:
       try:
          SD["currval"] += args[1]
       except:
          SD["currval"] = args[1]
  return SD["currval"]
' language 'plpython';

select runsum(1,0);
select num, runsum(0,num) from tallytable;

Variations on this technique are discussed on
General Bits http://www.varlena.com/GeneralBits
under the Tidbits area listing talks from OSCON2003.

elein@varlena.com


webstat=# select runsum(0,code), code, doc from temp_rawlogs;
n Sat, Oct 04, 2003 at 05:56:38PM +0800, Christopher Kings-Lynne wrote:
> Hi guys,
> 
> If I have a table that is just a single column full of numbers, how can 
> I select all the rows from the table with a second column that is the 
> running tally so far down the result set?
> 
> eg:
> 
> Num     Tally so far
> 0.3	0.3
> 1.2	1.5
> 2.0	3.5
> ...
> 
> Does this require PL/PgSQL coding?  If so, how do you actually construct 
> an arbitrary row for returning?  The docs are somewhat unclear on this.
> 
> Chris
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
>               http://archives.postgresql.org