Message-ID: <20050601092722.xk7duladyuzow0ow@www.presserv.org>
Date: Wed, 01 Jun 2005 09:27:22 -0300
From: lucas@presserv.org
To: pgsql-sql@postgresql.org
Subject: Re: Sum() rows
References: <20050531133648.cypdc5lnjtc0wsws@www.presserv.org>
	<429D0AD9.7070202@markdilger.com>
In-Reply-To: <429D0AD9.7070202@markdilger.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset=ISO-8859-1;
	format="flowed"
Content-Disposition: inline
Content-Transfer-Encoding: 7bit
User-Agent: Internet Messaging Program (IMP) H3 (4.0)

Hi.
The function works well...
I will use your function and rewrite it to accept more than one select, 
becouse
in this case you selected all records from tb1 table. In real case the 
table is
bigger with many fields and I will work with some filters and some ordering
(dynamically)...
Thank you.

> pgsql@markdilger.com wrote:
> CREATE TABLE tb1 (id integer primary key, value numeric);
>
> CREATE TYPE subtotal_type AS (id integer, value numeric, subtotal numeric);
>
> CREATE OR REPLACE FUNCTION subtotal () RETURNS SETOF subtotal_type AS $$
> DECLARE
>    tbrow    RECORD;
>    sbrow    subtotal_type;
> BEGIN
>    sbrow.subtotal := 0;
>    FOR tbrow IN
>      SELECT id, value FROM tb1 ORDER BY id
>    LOOP
>      sbrow.id := tbrow.id;
>      sbrow.value := tbrow.value;
>      sbrow.subtotal := sbrow.subtotal + tbrow.value;
>      RETURN NEXT sbrow;
>    END LOOP;
>    RETURN;
> END;
> $$ LANGUAGE plpgsql;
>
> insert into tb1 (id, value) values (1, 20.0);
> insert into tb1 (id, value) values (2, 2.0);
> insert into tb1 (id, value) values (3, 3.0);
>
> select * from subtotal();
>>
>> lucas@presserv.org wrote:
>> Hi.
>> How can I sum a row and show the sum for each row???
>> For example, in a finances table that have the total 
>> movimentation(debit/credit)
>> in the bank.
>>
>> i.e:
>> 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
>>
>> Any idea???
>>
>> Thanks.