Re: Sum() rows - Mailing list pgsql-sql
From | Andrew Hammond |
---|---|
Subject | Re: Sum() rows |
Date | |
Msg-id | 429CD15B.2070503@ca.afilias.info Whole thread Raw |
In response to | Sum() rows (lucas@presserv.org) |
List | pgsql-sql |
-----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-----