I find out a solution to multiply numbers in SQL on StackOverflow.com. And I asked myself: Why does it works?

EXP(SUM(LOG(Number)))

The task was to multiply the numbers on different rows. So imagine you have the following table, and with 1 simple query you get the product of all the numbers grouped by Id.

Id | Number |

1 | 1.5 |

2 | 2 |

3 | 10 |

1 | 5 |

2 | 4 |

1 | 1.6 |

1 | 1.8 |

2 | 3 |

3 | 7 |

So basically, you would take all the numbers for an Id, and multiply them:

1: 1.5 X 5 X 1.6 X 1.8 = 21.6

2: 2 X 4 X 3 = 24

3: 10 X 7 = 70

And to get back the following result:

Id | Product |

1 | 21.6 |

2 | 24 |

3 | 70 |

But in SQL, it’s a row based query. So one row doesn’t know the content of the next. So the solution says do the EXP of the sum of the LOG of each Id.

So it goes like that:

EXP(LOG(10) + LOG(7)) = 10 X 7

EXP(1 + 0.8450980) = 10 X 7

70 = 10 X 7

Or

EXP(LOG(10) + LOG(7)) = 10 X 7

LOG(10) + LOG(7) = LOG(10 X 7)

LOG(10) + LOG(7) = LOG(70)

A more precise writing of this would be:

EXP10(LOG10(10) + LOG10(7)) = 10 X 7

**It does works, but why?**

I was looking to have a more detailed explication, but it’s a simple rule of the logarithm. The sum of the logarithm is the same as the logarithm of the product.

So basically, Log10(10) + Log10(7) = Log10(10 X 7) = Log10(70).

So after that, since the EXP(LOG(n)) = n. You bring back the number by doing the opposite operation, using EXP.

**References**: