Re: Calculating product from rows - (aggregate product )

At the moment I have two probable solutions, the first makes use of
cursors and requires looping though each record, the other a
not-so-elegant solution (and may be unfavourable for large datasets)
makes use of arrays and  the EXECUTE command  (in plpgsql).

The second solution is as follows.

DROP table imaginary;

CREATE temp table imaginary(id INTEGER NOT NULL, some_field FLOAT
NULL,primary key(id));

INSERT INTO imaginary(id,some_field)VALUES(1,0.333);INSERT INTO
imaginary(id,some_field)VALUES(2,0.667);INSERT INTO
imaginary(id,some_field)VALUES(3,0.4);INSERT INTO
imaginary(id,some_field)VALUES(4,null);


SELECT array_to_string(ARRAY(SELECT a.some_field FROM imaginary a),'*');


--within plpgsql execute the following

EXECUTE 'SELECT '||SELECT array_to_string(ARRAY(SELECT a.some_field FROM
imaginary a),'*') INTO _my_aggregated_product;


Allan.





Allan Kamau wrote:
> 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.
>
>
> Allan.
>
>