TSQL Provides you mathematical functions like SUM(), AVG() etc. but it does not provide you a way to calculate Product of a list of values. Most of us know from our high school mathematics that Multiplication can be converted into a series of addition. If you use remember basic logarithms then you can easily calculate the product of the values. To refresh your concepts of logarithms please visit wikipedia here

Now, following is a sample code to demonstrate how you can calculate the product of the values by TSQL

Please be aware that since logarithms involves decimal numbers therefore, the result will not be 100% accurate but it will serve the purpose in most of the cases.

–Declare a sample table

Declare @m_TestTable table (sampleValue int)

–Fill the sample table with 10 values

Declare @m_value int

Select @m_value=1

While (@m_value < 10)

Begin

Insert into @m_TestTable values(@m_value)

Select @m_value=@m_value+1

End

–Calculate the product

Select Power(10,Sum(Log10(sampleValue))) from @m_TestTable

### Like this:

Like Loading...

*Related*

Is such variant worse?

Declare @m_TestTable table (sampleValue int)

–Fill the sample table with 10 values

Declare @m_value int

Select @m_value=1

While (@m_value < 10)

Begin

Insert into @m_TestTable values(@m_value)

Select @m_value=@m_value+1

End

select * from @m_TestTable

declare @product int

set @product = 1

select @prod = @prod * sampleValue from @m_TestTable

select @prod

Comment by Ivanoff — May 23, 2009 @ 11:01 PM |

This is a useful tip, but it won’t work for negative numbers minor or equal to minus one (-1). Any tips on working this out with larger negative and positive float numbers combined?

Comment by Carlos Alvidrez — October 25, 2010 @ 12:46 AM |