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”
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
.