There is no direct way to multiply all the values of a column as we do summation using SUM(col). But it is possible with the following methods
create table test (i int, amount float)
insert into test(i,amount)
select
abs(cast(cast(newid() as varbinary(100)) as smallint)),
abs(cast(cast(newid() as varbinary(100)) as smallint))/1000
from
sysobjects s1 cross join sysobjects s2
Method 1
select
i,exp(sum(log(cast(amount as float)))) AS multiplied_value
from
test
where
amount<>0
group by i
order by i
Method 2 use user defined function
create function dbo.multiply
(
@number int
)
returns float
as
begin
declare @i float
set @i=1.0select @i=@i*amount from test where i=@number
return @iend
select i,dbo.multiply(i) as multiplied_value from(select distinct i from test) as torder by i