CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-bysum
help_me
help_me

Reputation: 3

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.

Upvotes: 0

Views: 2075

Answers (2)

RBarryYoung
RBarryYoung

Reputation: 56725

There’s no PRODUCT() or MULTIPLY() aggregate function in SQL because it’s so likely to overflow. However, as you were trying to do, this can be done with LOG and EXP,

SELECT EXP(SUM(LOG(price_rate))) as price_factor
FROM yourTable

Note that using LOG/EXP like this with integer values will not usually produce exact integer results.

Upvotes: 2

GMB
GMB

Reputation: 222402

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.

Upvotes: 1

Related Questions