devxlogo

Multiply Values of a Particular Column in SQL Server

Multiply Values of a Particular Column in SQL Server

There is no T-SQL function for getting product of rows in a particular column. For example, say you had a table like this:

 --/////////////////////////////select * from #tbltest order by mydate ascID    MyDate	Value1     1/1/99     102     1/1/99     203     1/1/99     304     2/1/99     405     2/1/99     50--/////////////////////////////

If you want to get product of values for each date, here’s a simple way:

 --/////////////////////////////select mydate, convert(varchar,exp(sum(log(value)))) as productfrom #tblTestgroup by mydate--/////////////////////////////

Added to above solution, the following script is another way to check for zero and negative values using the sign function.

 --/////////////////////////////select mydate,convert(varchar,case sum(case when sign(value) = -1 then 1else 0 end)%2when 1 then -1 else 1 end * exp(sum(log(abs(case when sign(value) <> 0 then value end)))) * min(case when value = 0 then 0 else 1 end)) as productfrom #tblTestgroup by mydateorder by mydate asc--/////////////////////////////
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist