Re: MULT() instead of SUM()

gaussent wrote:
>
> Hi,
>
> I'm Chris from France.
> I would like to simulate an aggregate function which multiply instead
> off add.
> I would like to use it in query
>
> A function like SUM() which could be called MULT()
>
> SELECT NAME, MULT(QTY)
> FROM TABLE
> GROUP BY NAME
>
> Give me the result QTY * QTY * .... * QTY for each name.
>
> Is it possible and how can I do this.
>
> Thank you for your help.
>
> Christophe...

Something like...

CREATE OR REPLACE FUNCTION mult
(p_name IN VARCHAR2)
RETURN NUMBER IS

CURSOR c1 IS

SELECT qty
FROM table
WHERE name = p_name;

v_product NUMBER := 1;

BEGIN FOR r1 IN c1 LOOP

   v_product := v_product*r1.qty;

END LOOP; RETURN v_product;

END mult;

/

your SQL statement then looks like

SELECT name, mult(name)
FROM table
WHERE ...
GROUP BY name

Hope that helps,

MF

-- 
matt_foster_uk_at_yahoo.co.uk