Yes,
I tried it. In this table the query works fine, but in a big table
(with aprox.
200.000 records) the query performace is very bad.
I tried it (in the example table): SELECT *,(select sum(value) from tb1 as tb1_2 where tb1_2.id<=tb1_1.id) as
subtot from tb1 as tb1_1 order by id;
In a small table it works fine, but in a bigger table it works very slow.
I was thinking to create a temporary table and a function to update the value
for each row of the query... something like: CREATE table temporary (id serial primary key,value numeric default 0);
INSERTinto temporary values (1,0); CREATE or replace function temporary_sum(numeric) returns numeric as $$ BEGIN
updatetemporary set value = value+$1 where id=1; return value from temporary where id=1; END; $$ language
'plpgsql';
Then before execute the query I need to update the table's value to 0. UPDATE temporary set value=0; SELECT
*,temporary_sum(value)from tb1;
It works better than the "sum() subquery", but it not seems correct.
What is the better way??? Is there a sum() function that works how I want???
Thanks.
Quoting Bruno Wolff III <bruno@wolff.to>:
> Since in your example the id field gives the ordering, you can use a
> subselect
> to add up the subtotal for rows with and id less than or equal to the value
> of id for the current row.
>
>> 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
>>