Message-ID: <429D12D2.4020705@markdilger.com>
Date: Tue, 31 May 2005 18:43:46 -0700
From: Mark Dilger <pgsql@markdilger.com>
User-Agent: Mozilla Thunderbird 1.0 (X11/20050130)
MIME-Version: 1.0
To: Mark Dilger <pgsql@markdilger.com>
Cc: lucas@presserv.org, 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>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Mark Dilger wrote:
> 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
>>
>> 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
> 
> 
> 
> 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();
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
>               http://www.postgresql.org/docs/faq

I forgot to mention that you must run 'createlang plpgsql' on the 
database for this to work, if the language named plpgsql has not already 
been created.  (This command would typically be run from a shell, such 
as sh, bash, etc., and not from within psql.)

Please review 
http://www.postgresql.org/docs/8.0/interactive/app-createlang.html for 
more information about this command.

Cheers!