Compute the product of a column of data using SQL

January 12, 2014
SQL

In SQL, summing a column of numbers is relatively trivial, and the size of the dataset isn’t important. For example:

SELECT 
  SUM(X) as SUMOFX
FROM data_table;

Mathematically, what we are doing here is:

$$SUMOFX = \sum_{i=1}^n X_i$$

Now, if we find ourselves in the unfortunate situation to compute this:

$$PRODUCTOFX = \prod_{i=1}^n X_i$$

In this case, it won’t be as easy to use the SQL code above, but we can use Euler’s number and a property of the natural logarithm to also make this task a trivial one. The equation that we are going to exploit is:

$$ln(x*y) = ln(x) + ln(y)$$

which gives us a functional relationship between the product and sum of two numbers. This allows us to change our above SQL code to:

SELECT
  exp(sum(log(X))) as PRODUCTOFX
FROM data_table;

It’s probably really rare for this to be useful in any sort of production environment, however, I hope that this trick could save someone the hassle of having to code up a custom solution to compute a product for data that’s already in a relational database.

Good luck!

Short script to backup MySQL database from Python

November 3, 2010
SQL MySQL Python