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.