i was shocked to find that there is no aggregate multiplication functions in SQL, something like

select mul(number) from table

but i needed to use a multiplication function…, so as usual i googled it and found this statement

select exp(sum(log(cast(number as float)))) from table

that does the multiplication,

so with a mouth half opened i tested this line and it worked :D

so how does it work, it uses the property of the natural logarithm “log(a) + log(b) = log(ab)”

so simply it applies the scaler “log” function on the current value, then uses the aggregate function “sum” on all the “log” values computed then raising the whole result to the power of “e” to get rid of the log using the property “e(log(x))=x”

so for the first example would be

e(log(a) + log(b)) = e(log(ab)) = ab

so at the end the aggregate multiplication would be “product = e(∑(log(num))”

about the innermost function it’s just to cast the number to a float for the “log” function

i’m just wondering… why isn’t it already implemented ?

but the most funny thing, i wanted to understand the math behind this statement in work, i took some time writing it and trying to analyze it during the day at work without any clue, but as soon as i ate after being fasten “Iftar”, it just flashed into my brain without even thinking in it…

so add to the benefits of milk on Iftar… it makes you capable of understanding SQL combined aggregate functions :D