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);
INSERT into temporary values (1,0);
CREATE or replace function temporary_sum(numeric) returns numeric as
$$
BEGIN
update temporary 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(at)wolff(dot)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
>>