1. Registered User
Join Date
Oct 2002
Posts
15

Given a fictional table such as : (ID INTEGER, VALUE DEC(16,10) )
that contains multiple IDs, each with a varying number of up to say 10 value records per ID.

I need to do some probability math that requires multiplying each of the VALUE records by the next, grouped by ID.
So if ID = 5 and contains 3 records being :
0.2494747899
0.281512605
0.3159138655

I need to be able to calculate (0.2494747899*0.281512605*0.3159138655) returning a result of : 5,0.022186724
I can kind of figure how to do so but only for individual IDs and having to execute a recursion UDF, that has to be executed for each ID.

Is there a way using say a built in scalar function of sorts to do so over the entire table in one single SQL statement ?

Many thanks, Fin.

"If there is no such a requirement to use a recursion, you can use some useful math from the school like this: Code: ```select id, power(10, sum(log10(value))) from my_table group by id``` The idea used here is that the logarithm of a product is the sum of logarithms of the factors."

3. Registered User
Join Date
Jul 2013
Location
Moscow, Russia
Posts
666
If there is no such a requirement to use a recursion, you can use some useful math from the school like this:
Code:
```select id, power(10, sum(log10(value)))
from my_table
group by id```
The idea used here is that the logarithm of a product is the sum of logarithms of the factors.

4. Registered User
Join Date
Oct 2002
Posts
15
Hi Mark,

WOW ! .. heh I thought there might have been something that would be able to do what I had hoped, but had no idea what it was and would never have come up with such an elegant and apparently straight forward solution. I really must return to school obviously I must have missed this lesson .. heh

Thanks again, perfect.

5. Registered User
Join Date
Nov 2004
Posts
76
Elegant solution, Mark,

Sometimes you have to step outside of your usual ways to get a solution. You figured out how to do it.

Ferdinand

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•