From: Andrew Hammond <ahammond@ca.afilias.info>
To: lucas@presserv.org
Cc: pgsql-sql@postgresql.org
Subject: Re: Sum() rows
Date: Tue, 31 May 2005 17:04:27 -0400
Message-ID: <429CD15B.2070503@ca.afilias.info> (raw)
In-Reply-To: <20050531133648.cypdc5lnjtc0wsws@www.presserv.org>
References: <20050531133648.cypdc5lnjtc0wsws@www.presserv.org>
-----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-----
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ahammond@ca.afilias.info, lucas@presserv.org
Subject: Re: Sum() rows
In-Reply-To: <429CD15B.2070503@ca.afilias.info>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox