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.