Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: Enterprise
Expertise: Intermediate
Jan 30, 2003



Building the Right Environment to Support AI, Machine Learning and Deep Learning

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 asc
ID    MyDate	Value
1     1/1/99     10
2     1/1/99     20
3     1/1/99     30
4     2/1/99     40
5     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 product
from #tblTest
group 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 1
else 0 end)%2
when 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 product
from #tblTest
group by mydate
order by mydate asc

Harinatha Reddy
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date