A mathematical question

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:

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s