Re: Calculating product from rows - (aggregate product ) [solution found, thanks Andreas] - Mailing list pgsql-general

From Allan Kamau
Subject Re: Calculating product from rows - (aggregate product ) [solution found, thanks Andreas]
Date
Msg-id 49FEBEF1.70009@sanbi.ac.za
Whole thread Raw
List pgsql-general
On Mon, May 4, 2009 at 10:42 AM, A. Kretschmer
<andreas.kretschmer@schollglas.com> wrote:
 > In response to Allan Kamau :
 >> Hi
 >>
 >> I would like to calculate a product of a field's values of a relation,
 >> this function may multiply each value and give the result as a single
 >> float number.
 >>
 >> For example:
 >>
 >>
 >> CREATE table imaginary(id INTEGER NOT NULL, some_field FLOAT
 >> NULL,primary key(id));
 >>
 >> INSERT INTO imarginary(1,0.333);INSERT INTO imarginary(2,0.667);INSERT
 >> INTO imarginary(3,0.4);
 >>
 >>
 >> SELECT prod(some_field) FROM imarginary;
 >>
 >>
 >> would give 0.0888444 (which is 0.333*0.667*0.4)
 >>
 >>
 >> Is there an already existing function that does this.
 >
 > No, you need a own aggregate function, but it is easy:
 >
 > test=# CREATE FUNCTION multiply_aggregate(float,float) RETURNS float AS
 > ' select $1 * $2; ' language sql IMMUTABLE STRICT; CREATE AGGREGATE
 > multiply (basetype=float, sfunc=multiply_aggregate, stype=float,
 > initcond=1 ) ;
 > CREATE FUNCTION
 > CREATE AGGREGATE
 > test=*# create table float_test(a float);
 > CREATE TABLE
 > test=*# copy float_test from stdin;
 > Enter data to be copied followed by a newline.
 > End with a backslash and a period on a line by itself.
 >>> 0.333
 >>> 0.4
 >>> 0.8
 >>> \.
 > test=*# select multiply(a) from float_test;
 >  multiply
 > ----------
 >  0.10656
 > (1 row)
 >
 >
 >
 > Regards, Andreas
 > --
 > Andreas Kretschmer
 > Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
 > GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net
 >
 > --
 > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 > To make changes to your subscription:
 > http://www.postgresql.org/mailpref/pgsql-general
 >


Thanks Andreas, your solution works quite well, much appreciated.

Allan


pgsql-general by date:

Previous
From: "A. Kretschmer"
Date:
Subject: Re: Calculating product from rows - (aggregate product )
Next
From: "Markus Wollny"
Date:
Subject: diff-/patch-functionality for text-type data inside PostgreSQL