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

On Mon, May 4, 2009 at 10:42 AM, A. Kretschmer
<andreas(dot)kretschmer(at)schollglas(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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

Allan