SQL Multiply all values in a column

Hi,

Most of you reading this blog must be aware that SQL Server has an aggregate function called SUM to add all the values in a column.But what if you want to multiply all the values in a column.

Well still no inbuilt function has been available as yet but one can use the following query to achieve it.

Declare @t table(value int)
 
insert @t
 
select 10 union

select 5 union
 
select 2
 
  
select EXP(sum(log(value))) from @t ---100

I will go a bit off topic and do a bit of diving into mathematics so that you really understand how SQL really multiplys all the values in a column.

The idea behind this is that we take log values for the column, sum those log values and use a exponential function ex  to get the product of values.Since log and exponential are inverse function of each other exp returns power of value provided.
In maths log x + log y=log(x * y) & exponential function is inverse to log i.e  e^logx =x  so e^log(x * ^y) = x * y

So with our function exp(sum(log(values))) what we exactly do is create a log value for each value in column,sum them up and find the exponential value.
So now our function looks like this e^{(log x + log y)} where log x + log y =log(x * y) which gives the product of all the values in the column which have been computed to the log values.
Regards

Sachin Nandanwar

Like us on FaceBook  |  Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook