select product(value) from mytable

February 12, 2010

A nice tip from Harold Fuchs to calculate the product in Mysql

select exp(sum(log(coalesce(value,1))) from mytable

The coalesce() function is there to guard against trying to calculate the
logarithm of a null value and may be optional depending on your
circumstances.

Here’s an example

+------+-------+
| id   | value |
+------+-------+
|    1 |     3 |
|    1 |     2 |
|    2 |     5 |
|    3 |     7 |
|    3 |     3 |
+------+-------+

and as expected

select id,exp(sum(log(coalesce(value,1)))) from mytable group by id;

yields

+------+----------------------------------+
| id   | exp(sum(log(coalesce(value,1)))) |
+------+----------------------------------+
|    1 |                                6 |
|    2 |                                5 |
|    3 |                               21 |
+------+----------------------------------+