From: PFC <lists@boutiquenumerique.com>
To: lucas@presserv.org
To: pgsql-sql@postgresql.org
Subject: Re: Sum() rows
Date: Wed, 01 Jun 2005 01:11:45 +0200
Message-ID: <op.srnxdvy6th1vuj@localhost> (raw)
In-Reply-To: <20050531133648.cypdc5lnjtc0wsws@www.presserv.org>
References: <20050531133648.cypdc5lnjtc0wsws@www.presserv.org>
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
>
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: lists@boutiquenumerique.com, lucas@presserv.org
Subject: Re: Sum() rows
In-Reply-To: <op.srnxdvy6th1vuj@localhost>
* 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