Re: MULT() instead of SUM()

Matt Foster wrote:
>
> 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,
>

Oops. Just realised this will not work if the WHERE clause of the SQL statement references anything other than name.

MF

-- 
matt_foster_uk_at_yahoo.co.uk