Re: Sum() rows

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
>>