To: lucas@presserv.org, pgsql-sql@postgresql.org
Subject: Re: Sum() rows
References: <20050531133648.cypdc5lnjtc0wsws@www.presserv.org>
Message-ID: <op.srnxdvy6th1vuj@localhost>
Date: Wed, 01 Jun 2005 01:11:45 +0200
From: PFC <lists@boutiquenumerique.com>
Organization: =?iso-8859-15?Q?La_Boutique_Num=E9rique?=
Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
In-Reply-To: <20050531133648.cypdc5lnjtc0wsws@www.presserv.org>
User-Agent: Opera M2(BETA2)/8.0 (Linux, build 987)


The simplest would be to create a stored procedure like this :

declare  row as TB1%rowtype, and ret as (id integer, value numeric, subtot  
numeric) then :

ret.subtot = 0

FOR row IN SELECT * FROM TB1 ORDER BY id DO
	ret.id = row.id
	ret.value = row.value
	ret.subtot = ret.subtot + row.value
	RETURN NEXT ret
END

etc...

SQL doesn't really work well for this kind of things whereas plpgsql works  
really well and it's plenty fast too.


> CREATE TABLE TB1 (id integer primary key, value numeric);
> insert into tb1 values (1,20);
> insert into tb1 values (2,2);
> insert into tb1 values (3,3);
> insert into tb1 values (4,17);
> insert into tb1 values (5,-0.5);
> insert into tb1 values (6,3);
>
> I want a query that returns:
> -id- | --- value --- | --- subtot ---
>    1 |        20.00  |         20.00
>    2 |         2.00  |         22.00
>    3 |         3.00  |         25.00
>    4 |        17.00  |         42.00
>    5 |        -0.50  |         41.50
>    6 |         3.00  |         44.50
>
> The subtot colum will be the "prev. subtot colum"+"value colum". :-/
> I dont know how to make the "subtot" colum, I tried to use the sum()  
> function
> but it not works correctly.
> Any idea???
>
> Thanks.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>