From: Mark Dilger <pgsql@markdilger.com>
To: Mark Dilger <pgsql@markdilger.com>
Cc: lucas@presserv.org
Cc: pgsql-sql@postgresql.org
Subject: Re: Sum() rows
Date: Tue, 31 May 2005 18:43:46 -0700
Message-ID: <429D12D2.4020705@markdilger.com> (raw)
In-Reply-To: <429D0AD9.7070202@markdilger.com>
References: <20050531133648.cypdc5lnjtc0wsws@www.presserv.org>
<429D0AD9.7070202@markdilger.com>
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!
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pgsql@markdilger.com, lucas@presserv.org
Subject: Re: Sum() rows
In-Reply-To: <429D12D2.4020705@markdilger.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox