Skip to content
Advertisement

How to multiply all values in one column to make one number?

In SQL Server, I am trying to multiply all values of a calculated column in a table in descending date order, to produce one number.

This is so I can calculate a factor that can be applied to a different table / value, i.e. multiplying every value in the “price rate” column to produce a “price factor”

enter image description here

So far I have been trying to use exp AND log functions with no luck.

Advertisement

Answer

In absence of an aggregate “product” function in SQL, one method uses arithmetic: you can sum the logarithm of each value, then take the the exponential of the result.

select exp(sum(ln(price_rate))) as price_factor
from mytable

FOr this to work properly, all values of price_rate must be greater than 0.

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement