Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


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

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.

 

 

Sitemap