Re: aggregate function

select sum(col4), col1, col2,col3
from table
group by col1, col2, col3;

select f(col1,col2,col3), col1, col2,col3 from table
group by col1, col2, col3;

where f(a,b,c) is defined to execute

    select col4
    where col1 = a
    and col2 = b
    and col3 = c

then sum the col4 values of the fetched rows and return the result

The outputs will be identical.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Alex Filonov wrote in message <91t99c$9rr$1_at_nnrp1.deja.com>...


>Doesn't look like a group function to me. The trick is to process

>DIFFERENT values for each group. Like Oracle group functions SUM, MAX, etc.

>

>In article <977350459.11325.0.nnrp-13.9e984b29_at_news.demon.co.uk>,

>  "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

>>

>> It is generally possible - but there is a limitation

>> that makes the specific example fail.

>>

>> You can write a function to do:

>>

>> select f(col1, col2, col3), col1, col2,col3

>> from table

>> group by col1, col2, col3;

>>

>> The function simply has to embed a

>>

>>     select {requirements} from table

>>     where col1 = parm1

>>     and col2  = parm2

>>     and col3 = parm3

>>

>> It seems to work quite efficiently as the

>> function is called AFTER the group by,

>> i.e. just once for each combination of

>> col1, col2, col3.

>>

>> --

>> Jonathan Lewis

>> Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

>>

>> Practical Oracle 8i:  Building Efficient Databases

>>

>> Publishers:  Addison-Wesley

>> See a first review at:

>> http://www.ixora.com.au/resources/index.htm#practical_8i

>> More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html

>>

>> Alex Filonov wrote in message <91r5up$ldk$1_at_nnrp1.deja.com>...

>> >As far as I know, you can't.

>> >

>> >In article <91qgc1$16n$1_at_nnrp1.deja.com>,

>> >  slezakp_at_my-deja.com wrote:

>> >> Can one write an aggregate function in Oracle 8.1.6?

>> >>

>> >> Example:  normal sum

>> >>

>> >> select sum(tools) from powertools;

>> >>

>> >> I want to be able to do:

>> >>

>> >> select mysum(tools) from powertools.

>> >>

>> >> Note that mysum will not know what table or column ahead of time.

>> >>

>> >> Thanks,

>> >> Paul

>> >>

>> >> Sent via Deja.com

>> >> http://www.deja.com/

>> >>

>> >

>> >

>> >Sent via Deja.com

>> >http://www.deja.com/

>>

>>

>

>

>Sent via Deja.com

>http://www.deja.com/