In Oracle, there exists no analog of SUM for multiplication, i.e., there isn’t a function that takes a fieldname as an argument and returns the product of all values in that column. However, obtaining the product of all items in a column can be obtained by calling:

EXP(SUM(LN(COLUMN_1)))


For example, if you have a table TABLE_1, and need to obtain the product of all items in COLUMN_1, the query would be:

SELECT EXP(SUM(LN(COLUMN_1))) FROM TABLE_1;



The result is obtained by leveraging the properties of exponentials and logarithms and the inverse relationship they have to one another. Recall that:

$$ e^{Ln(x)} = Ln(e^{x}) = x $$

There are two properties we’ll utilize for our demonstration:

The natural logarithm maps multiplication into addition:

$$ Ln\big(\prod_{i=1}^{n} x_{i}\big) = \sum_{i=1}^{n} Ln(x_{i}) $$

For the exponential function we exploit the algebraic property:

$$ \prod_{i=1}^{n} e^{x_{i}} = e^{\sum_{i=1}^{n} x_{i}} $$

Focusing on the EXP(SUM(LN(COLUMN_1))) expression in our query, we’ll work from the inside out.

Our query calculates LN(COLUMN_1) for all values in COLUMN_1, then sums over all of the natural-logged values. Then we exponentiate the sum of natural logs:

$$ e^{\sum Ln(x_{i})}. $$

Because of the algebraic property of the exponential, \(\prod_{i=1}^{n} e^{x_{i}} = e^{\sum_{i=1}^{n} x_{i}}\), the exponentiated sum of natural logs becomes:

$$ e^{\sum Ln(x_{i})} = e^{Ln(x_{1})}e^{Ln(x_{2})} \cdots e^{Ln(x_{n})}, $$

and since \(e^{Ln(x)} = x\), the expression simplifies to:

$$ e^{Ln(x_{1})}e^{Ln(x_{2})} \cdots e^{Ln(x_{n})} = x_{1}x_{2} \cdots x_{n} $$

for all \(n\) rows in COLUMN_1, which is the product of all values in COLUMN_1.