Home » Tip Bank » Security » SQL Server

Language: Enterprise
Expertise: Intermediate

Jan 29, 2003

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

--/////////////////////////////