SQL Tips by Namwar Rizvi

August 9, 2007

Calculate Product of the numeric values by TSQL

Filed under: Information,Performance,Query,tips,TSQL — namwar @ 9:55 PM

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

Advertisements

2 Comments »

  1. 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 | Reply

  2. 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 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: