Message-ID: <429D0AD9.7070202@markdilger.com>
Date: Tue, 31 May 2005 18:09:45 -0700
From: Mark Dilger <pgsql@markdilger.com>
User-Agent: Mozilla Thunderbird 1.0 (X11/20050130)
MIME-Version: 1.0
To: lucas@presserv.org
Cc: pgsql-sql@postgresql.org
Subject: Re: Sum() rows
References: <20050531133648.cypdc5lnjtc0wsws@www.presserv.org>
In-Reply-To: <20050531133648.cypdc5lnjtc0wsws@www.presserv.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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();