Simple Multiply Function

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.0
select @i=@i*amount from test where i=@number
return @i
end


select i,dbo.multiply(i) as multiplied_value from
(
select distinct i from test
) as t
order by i
Published 13 November 2007 13:37 by Madhivanan
Filed under: ,

Comments

# re: Simple Multiply Function

13 November 2007 15:36 by Hugo Kornelis

Hi Madhivanan,

Method 1 will only work for positive values. Here is a version that calculates a product for all values: positive, negative, and zero:

SELECT   i,

 CASE

   WHEN MAX(CASE WHEN amount = 0 THEN 1 END) = 1 THEN 0

   ELSE

     CASE WHEN COUNT(CASE WHEN amount < 0 THEN 1 END) % 2 = 0

       THEN 1 ELSE -1

     END * EXP(SUM(LOG(NULLIF(ABS(amount),0))))

 END AS multiplied_value

FROM     test

GROUP BY i

ORDER BY i;

Or you can use this, shorter but harder to understand, version:

SELECT   i,EXP(SUM(LOG(ABS(NULLIF(amount,0)))))*(1-SUM(1-SIGN(CAST(amount AS decimal(38,30))))%4)*(1-SUM(1-SQUARE(SIGN(amount))))

FROM     test

GROUP BY i

ORDER BY i;

Both these queries are based on queries in "Inside Microsoft SQL Server 2005: T-SQL Querying", by Itzik Ben-Gan et al. If they don't work, I probably made an error while copying the query.

Best, Hugo

# re: Simple Multiply Function

19 November 2007 10:01 by Madhivanan

Hi Hugo

Thanks

# re: Simple Multiply Function

28 November 2007 15:30 by jeng02

I'm kind of a newbie, and didn't understand what EXP(SUM(LOG(x))) does, but now I understand that it's used to fill in for the lack of their being a SQL "Product" or "Multiply" aggregate function.

It doesn't work for zero or negative, of course, but in my application I don't need it to.

This was also helpful:

http://books.google.com/books?id=EoSNYVb5LAsC&pg=PA83&dq=%22multiply+across+a+result+set%22&ei=uohNR9q5DoW0oQL8sdD4Bg&sig=YORDTiTts5x-LIC6_zc5lRMIoSk

# re: Simple Multiply Function

29 November 2007 06:16 by Madhivanan

Hi jeng02,

Read Hugo's reply that solves your problem

And thanks for that link Smile