Thread: Sum() rows
Hi. How can I sum a row and show the sum for each row??? For example, in a finances table that have the total movimentation(debit/credit) in the bank. 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 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.
On Tue, May 31, 2005 at 13:36:48 -0300, lucas@presserv.org wrote: > Hi. > How can I sum a row and show the sum for each row??? 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. > For example, in a finances table that have the total movimentation(debit/credit) > in the bank. > > 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 > > 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
On Tue, May 31, 2005 at 01:36:48PM -0300, lucas@presserv.org wrote: > Hi. > How can I sum a row and show the sum for each row??? > For example, in a finances table that have the total movimentation(debit/credit) > in the bank. I think you can write an aggregate in, say PL/Python to do this. IIRC there was an article on General Bits about this. Search for "running aggregates". I think there is something in issue #23. See #29 as well. -- Alvaro Herrera (<alvherre[a]surnet.cl>) "Las cosas son buenas o malas segun las hace nuestra opinión" (Lisias)
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
lucas@presserv.org wrote:
> Hi.
> How can I sum a row and show the sum for each row???
> For example, in a finances table that have the total movimentation(debit/credit)
> in the bank.
>
> 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
>
> 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???
This kind of thing is often done using views and rules. For example,
CREATE TABLE tb1_real ( id serial primary key, value numeric, subtot numeric
);
CREATE VIEW tb1 AS
SELECT id, value
FROM tb1_real;
CREATE RULE tb1_insert AS
ON INSERT TO tb1 DO INSTEAD
INSERT INTO tb1_real (id, value, subtot)
VALUES (COALESCE(NEW.id, nextval('tb1_real_id_seq')), NEW.value, NEW.value + COALESCE((SELECT subtot FROM tb1_real
ORDER BY id DESC LIMIT 1), 0));
/* ahammond@[local]:5432/ahammond =# */ INSERT INTO tb1 (value) VALUES (20);
INSERT 60812 1
/* ahammond@[local]:5432/ahammond =# */ INSERT INTO tb1 (value) VALUES
(-10);
INSERT 60813 1
/* ahammond@[local]:5432/ahammond =# */ SELECT * FROM tb1;id | value
- ----+------- 1 | 20 2 | -10
(2 rows)
/* ahammond@[local]:5432/ahammond =# */ SELECT * FROM tb1_real;id | value | subtot
- ----+-------+-------- 1 | 20 | 20 2 | -10 | 10
(2 rows)
- --
Andrew Hammond 416-673-4138 ahammond@ca.afilias.info
Database Administrator, Afilias Canada Corp.
CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (GNU/Linux)
iD8DBQFCnNFZgfzn5SevSpoRAk7ZAJ0aiDO41pajzvD0ioJsUJuaqrbLfACgl1yT
X6WGjU/Vog06apieWmQixF4=
=N5R4
-----END PGP SIGNATURE-----
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 DOret.id = row.idret.value = row.valueret.subtot = ret.subtot + row.valueRETURNNEXT 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 >
lucas@presserv.org wrote: > Hi. > How can I sum a row and show the sum for each row??? > For example, in a finances table that have the total movimentation(debit/credit) > in the bank. > > 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 > > 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 CREATE TABLE tb1 (id integer primary key, value numeric); CREATE TYPE subtotal_type AS (id integer, value numeric, subtotal numeric); CREATE OR REPLACE FUNCTION subtotal () RETURNS SETOF subtotal_type AS $$ DECLARE tbrow RECORD; sbrow subtotal_type; BEGIN sbrow.subtotal := 0; FOR tbrow IN SELECT id, value FROM tb1 ORDER BY id LOOP sbrow.id := tbrow.id; sbrow.value:= tbrow.value; sbrow.subtotal := sbrow.subtotal + tbrow.value; RETURN NEXT sbrow; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; insert into tb1 (id, value) values (1, 20.0); insert into tb1 (id, value) values (2, 2.0); insert into tb1 (id, value) values (3, 3.0); select * from subtotal();
Mark Dilger wrote: > lucas@presserv.org wrote: > >> Hi. >> How can I sum a row and show the sum for each row??? >> For example, in a finances table that have the total >> movimentation(debit/credit) >> in the bank. >> >> 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 >> >> 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 > > > > CREATE TABLE tb1 (id integer primary key, value numeric); > > CREATE TYPE subtotal_type AS (id integer, value numeric, subtotal numeric); > > CREATE OR REPLACE FUNCTION subtotal () RETURNS SETOF subtotal_type AS $$ > DECLARE > tbrow RECORD; > sbrow subtotal_type; > BEGIN > sbrow.subtotal := 0; > FOR tbrow IN > SELECT id, value FROM tb1 ORDER BY id > LOOP > sbrow.id := tbrow.id; > sbrow.value := tbrow.value; > sbrow.subtotal := sbrow.subtotal + tbrow.value; > RETURN NEXT sbrow; > END LOOP; > RETURN; > END; > $$ LANGUAGE plpgsql; > > > insert into tb1 (id, value) values (1, 20.0); > insert into tb1 (id, value) values (2, 2.0); > insert into tb1 (id, value) values (3, 3.0); > > select * from subtotal(); > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq I forgot to mention that you must run 'createlang plpgsql' on the database for this to work, if the language named plpgsql has not already been created. (This command would typically be run from a shell, such as sh, bash, etc., and not from within psql.) Please review http://www.postgresql.org/docs/8.0/interactive/app-createlang.html for more information about this command. Cheers!
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 >>
Hi. The function works well... I will use your function and rewrite it to accept more than one select, becouse in this case you selected all records from tb1 table. In real case the table is bigger with many fields and I will work with some filters and some ordering (dynamically)... Thank you. > pgsql@markdilger.com wrote: > CREATE TABLE tb1 (id integer primary key, value numeric); > > CREATE TYPE subtotal_type AS (id integer, value numeric, subtotal numeric); > > CREATE OR REPLACE FUNCTION subtotal () RETURNS SETOF subtotal_type AS $$ > DECLARE > tbrow RECORD; > sbrow subtotal_type; > BEGIN > sbrow.subtotal := 0; > FOR tbrow IN > SELECT id, value FROM tb1 ORDER BY id > LOOP > sbrow.id := tbrow.id; > sbrow.value := tbrow.value; > sbrow.subtotal := sbrow.subtotal + tbrow.value; > RETURN NEXT sbrow; > END LOOP; > RETURN; > END; > $$ LANGUAGE plpgsql; > > insert into tb1 (id, value) values (1, 20.0); > insert into tb1 (id, value) values (2, 2.0); > insert into tb1 (id, value) values (3, 3.0); > > select * from subtotal(); >> >> lucas@presserv.org wrote: >> Hi. >> How can I sum a row and show the sum for each row??? >> For example, in a finances table that have the total >> movimentation(debit/credit) >> in the bank. >> >> 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 >> >> Any idea??? >> >> Thanks.
On Wed, Jun 01, 2005 at 08:49:00 -0300, lucas@presserv.org wrote: > 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. Not surprising, since this is probably O(n^2). > What is the better way??? Is there a sum() function that works how I want??? Having the application do the running sum is probably the best way to do it.