Product() aggregate function

The logarathm/power approach is the generally used approach. For Oracle, that is:

select exp(sum(ln(col)))from table;

I don't know why the original database designers didn't include PRODUCT() as an aggregation function. My best guess is that they were all computer scientists, with no statisticians. Such functions are very useful in statistics, but they don't show up much in computer science. Perhaps they didn't want to deal with overflow issues, that such a function would imply (especially on integers).

By the way, this function is missing from most databases, even those that implement lots of statistical aggregation functions.

edit:

Oy, the problem of negative numbers makes it a little more complicated:

select ((case when mod(sum(sign(col)), 2) = 0 then 1 else -1 end) *        exp(sum(ln(abs(col))))       ) as product

I am not sure of a safe way in Oracle to handle 0s. This is a "logical" approach:

select (case when sum(case when col = 0 then 1 else 0 end) > 0             then NULL             when mod(sum(sign(col)), 2) = 0             then exp(sum(ln(abs(col)))             else - exp(sum(ln(abs(col)))        end)        ) as product

The problem is that the database engine might get an error on the log before executing the case statement. That happens to be how SQL Server works. I'm not sure about Oracle.

Ah, this might work:

select (case when sum(case when col = 0 then 1 else 0 end) > 0             then NULL             when mod(sum(sign(col)), 2) = 0             then exp(sum(ln(case when col <> 0 then abs(col) end)))             else - exp(sum(ln(case when col <> 0 then abs(col) end)))        end)        ) as product

It returns NULL when there is a 0.

In Oracle you can create your own aggregate function,
please take a look at this working example: http://sqlfiddle.com/#!4/ee247/1
It is based on example from documentation:
http://docs.oracle.com/cd/B28359_01/appdev.111/b28425/aggr_functions.htm

create type ProductImpl as object(  product NUMBER,   static function ODCIAggregateInitialize(sctx IN OUT ProductImpl) return number,  member function ODCIAggregateIterate(self IN OUT ProductImpl, value IN number) return number,  member function ODCIAggregateTerminate(self IN ProductImpl, returnValue OUT number, flags IN number) return number,  member function ODCIAggregateMerge(self IN OUT ProductImpl, ctx2 IN ProductImpl) return number);/create or replace type body ProductImpl is static function ODCIAggregateInitialize(sctx IN OUT ProductImpl) return number is begin  sctx := ProductImpl(1);  return ODCIConst.Success;end;member function ODCIAggregateIterate(self IN OUT ProductImpl, value IN number) return number isbegin  self.Product := self.Product * value;  return ODCIConst.Success;end;member function ODCIAggregateTerminate(self IN ProductImpl,     returnValue OUT number, flags IN number) return number isbegin  returnValue := self.Product;  return ODCIConst.Success;end;member function ODCIAggregateMerge(self IN OUT ProductImpl, ctx2 IN ProductImpl) return number isbegin  self.Product := self.Product * ctx2.Product;  return ODCIConst.Success;end;end;/CREATE OR REPLACE FUNCTION Product (input NUMBER) RETURN NUMBER PARALLEL_ENABLE AGGREGATE USING ProductImpl;/

Oracle has exposed these ODCI (Oracle Data Cartridge Interface) methods to do the Aggregate! Reference:

• ODCIAggregateDelete() - Removes an input value from the current group.
• ODCIAggregateInitialize() - Initializes the aggregation context and instance of the implementation object type, and returns it as an OUT parameter.
• ODCIAggregateIterate() - Iterates through input rows by processing the input values, updating and then returning the aggregation context.
• ODCIAggregateMerge() - Merges two aggregation contexts into a single object instance during either serial or parallel evaluation of the user-defined aggregate.
• ODCIAggregateTerminate() - Calculates the result of the aggregate computation and performs all necessary cleanup, such as freeing memory.
• ODCIAggregateWrapContext() Integrates all external pieces of the current aggregation context to make the context self-contained.

Code For PRODUCT() Aggregate function :

CREATE OR REPLACE type PRODUCT_IMPLAS  object  (    result NUMBER,    static FUNCTION ODCIAggregateInitialize(sctx IN OUT PRODUCT_IMPL)    RETURN NUMBER,    member FUNCTION ODCIAggregateIterate(self  IN OUT PRODUCT_IMPL,                                         value IN NUMBER)    RETURN NUMBER,    member FUNCTION ODCIAggregateTerminate( self IN PRODUCT_IMPL,                                            returnValue OUT NUMBER,                                            flags IN NUMBER)    RETURN NUMBER,    member FUNCTION ODCIAggregateMerge(self IN OUT PRODUCT_IMPL,                                       ctx2 IN PRODUCT_IMPL )    RETURN NUMBER );  /  /* 1.Initializes the computation by initializing the aggregation context—the rows over which aggregation is performed: */CREATE OR REPLACE type body PRODUCT_IMPLIS  static FUNCTION ODCIAggregateInitialize(sctx IN OUT PRODUCT_IMPL)  RETURN NUMBERISBEGIN  sctx := PRODUCT_IMPL(1);  RETURN ODCIConst.Success;END;/* 2.Iteratively processes each successive input value and updates the context: */member FUNCTION ODCIAggregateIterate(self  IN OUT PRODUCT_IMPL,                                     value IN NUMBER)  RETURN NUMBERISBEGIN  self.result := value * self.result;  RETURN ODCIConst.Success;END;member FUNCTION ODCIAggregateTerminate(    self IN PRODUCT_IMPL,    returnValue OUT NUMBER,    flags IN NUMBER)  RETURN NUMBERISBEGIN  returnValue := self.result;  RETURN ODCIConst.Success;END;member FUNCTION ODCIAggregateMerge(self IN OUT PRODUCT_IMPL,                                   ctx2 IN PRODUCT_IMPL)  RETURN NUMBERISBEGIN  self.result := self.result;  RETURN ODCIConst.Success;END;END;//* Create A function using the PRODUCT_IMPL implementation we did above */CREATE OR REPLACE FUNCTION product(input NUMBER)RETURN NUMBER PARALLEL_ENABLE AGGREGATE USING PRODUCT_IMPL;/

Results:

SELECT group_name,product(num) FROM product_test GROUP BY group_name;Mahesh  -60000Mahesh_1    9