SQL Multiply all values in a column


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  elogx =x  so elog(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.

Sachin Nandanwar

Like us on FaceBook Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook


Leave a Reply

Your email address will not be published.