pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSum() rows
10+ messages / 6 participants
[nested] [flat]
* Sum() rows
@ 2005-05-31 16:36 lucas@presserv.org
2005-05-31 17:17 ` Re: Sum() rows Bruno Wolff III <bruno@wolff.to>
2005-05-31 20:17 ` Re: Sum() rows Alvaro Herrera <alvherre@surnet.cl>
2005-05-31 21:04 ` Re: Sum() rows Andrew Hammond <ahammond@ca.afilias.info>
2005-05-31 23:11 ` Re: Sum() rows PFC <lists@boutiquenumerique.com>
2005-06-01 01:09 ` Re: Sum() rows Mark Dilger <pgsql@markdilger.com>
0 siblings, 5 replies; 10+ messages in thread
From: lucas@presserv.org @ 2005-05-31 16:36 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Sum() rows
2005-05-31 16:36 Sum() rows lucas@presserv.org
@ 2005-05-31 17:17 ` Bruno Wolff III <bruno@wolff.to>
2005-06-01 11:49 ` Re: Sum() rows lucas@presserv.org
4 siblings, 1 reply; 10+ messages in thread
From: Bruno Wolff III @ 2005-05-31 17:17 UTC (permalink / raw)
To: lucas@presserv.org; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Sum() rows
2005-05-31 16:36 Sum() rows lucas@presserv.org
2005-05-31 17:17 ` Re: Sum() rows Bruno Wolff III <bruno@wolff.to>
@ 2005-06-01 11:49 ` lucas@presserv.org
2005-06-01 17:00 ` Re: Sum() rows Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 10+ messages in thread
From: lucas@presserv.org @ 2005-06-01 11:49 UTC (permalink / raw)
To: pgsql-sql
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@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
>>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Sum() rows
2005-05-31 16:36 Sum() rows lucas@presserv.org
2005-05-31 17:17 ` Re: Sum() rows Bruno Wolff III <bruno@wolff.to>
2005-06-01 11:49 ` Re: Sum() rows lucas@presserv.org
@ 2005-06-01 17:00 ` Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 10+ messages in thread
From: Bruno Wolff III @ 2005-06-01 17:00 UTC (permalink / raw)
To: lucas@presserv.org; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Sum() rows
2005-05-31 16:36 Sum() rows lucas@presserv.org
@ 2005-05-31 20:17 ` Alvaro Herrera <alvherre@surnet.cl>
4 siblings, 0 replies; 10+ messages in thread
From: Alvaro Herrera @ 2005-05-31 20:17 UTC (permalink / raw)
To: lucas@presserv.org; +Cc: pgsql-sql
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)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Sum() rows
2005-05-31 16:36 Sum() rows lucas@presserv.org
@ 2005-05-31 21:04 ` Andrew Hammond <ahammond@ca.afilias.info>
4 siblings, 0 replies; 10+ messages in thread
From: Andrew Hammond @ 2005-05-31 21:04 UTC (permalink / raw)
To: lucas@presserv.org; +Cc: 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-----
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Sum() rows
2005-05-31 16:36 Sum() rows lucas@presserv.org
@ 2005-05-31 23:11 ` PFC <lists@boutiquenumerique.com>
4 siblings, 0 replies; 10+ messages in thread
From: PFC @ 2005-05-31 23:11 UTC (permalink / raw)
To: lucas@presserv.org; pgsql-sql
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 DO
ret.id = row.id
ret.value = row.value
ret.subtot = ret.subtot + row.value
RETURN NEXT 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
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Sum() rows
2005-05-31 16:36 Sum() rows lucas@presserv.org
@ 2005-06-01 01:09 ` Mark Dilger <pgsql@markdilger.com>
2005-06-01 01:43 ` Re: Sum() rows Mark Dilger <pgsql@markdilger.com>
2005-06-01 12:27 ` Re: Sum() rows lucas@presserv.org
4 siblings, 2 replies; 10+ messages in thread
From: Mark Dilger @ 2005-06-01 01:09 UTC (permalink / raw)
To: lucas@presserv.org; +Cc: pgsql-sql
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();
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Sum() rows
2005-05-31 16:36 Sum() rows lucas@presserv.org
2005-06-01 01:09 ` Re: Sum() rows Mark Dilger <pgsql@markdilger.com>
@ 2005-06-01 01:43 ` Mark Dilger <pgsql@markdilger.com>
1 sibling, 0 replies; 10+ messages in thread
From: Mark Dilger @ 2005-06-01 01:43 UTC (permalink / raw)
To: Mark Dilger <pgsql@markdilger.com>; +Cc: lucas@presserv.org; pgsql-sql
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!
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Sum() rows
2005-05-31 16:36 Sum() rows lucas@presserv.org
2005-06-01 01:09 ` Re: Sum() rows Mark Dilger <pgsql@markdilger.com>
@ 2005-06-01 12:27 ` lucas@presserv.org
1 sibling, 0 replies; 10+ messages in thread
From: lucas@presserv.org @ 2005-06-01 12:27 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2005-06-01 17:00 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-05-31 16:36 Sum() rows lucas@presserv.org
2005-05-31 17:17 ` Bruno Wolff III <bruno@wolff.to>
2005-06-01 11:49 ` lucas@presserv.org
2005-06-01 17:00 ` Bruno Wolff III <bruno@wolff.to>
2005-05-31 20:17 ` Alvaro Herrera <alvherre@surnet.cl>
2005-05-31 21:04 ` Andrew Hammond <ahammond@ca.afilias.info>
2005-05-31 23:11 ` PFC <lists@boutiquenumerique.com>
2005-06-01 01:09 ` Mark Dilger <pgsql@markdilger.com>
2005-06-01 01:43 ` Mark Dilger <pgsql@markdilger.com>
2005-06-01 12:27 ` lucas@presserv.org
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