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 <[EMAIL PROTECTED]>:
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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
