views:

403

answers:

3

Suppose I have a table like the following:

tblNumbers

Numbers
4  
5
3
6

Using SET BASED approach how can I perform a multiplication so the output will be:

Output
360

N.B~ There is no hard and fast rule that there will be only four numbers, but I'd prefer the answer to be using a CTE and/or correlated subquery.

+2  A: 
declare @result int
set @result = 1

select @result = @result * [number]
from tblNumber

print @result

(note that this assumes an int column and no overflow)

Michael Todd
without declaring the @result, cannot we go for any other approach?Like I don't want to take help of any variable. I am basically trying to get the solution using recursive CTE. But as of now no luck. I have to do it by using something like CTE/Corelated subquery etc.
priyanka.sarkar
A: 

Michael's result is efficient.

You can use a recursive CTE, simply define a ROW_NUMBER and self-join. But why bother, it won't be as efficient, since you need to do a table or index scan anyway.

Cade Roux
i don't know if user can make user-defined aggregate in mssql, this is more efficient http://stackoverflow.com/questions/1490875/why-the-result-of-exp-log-of-postgres-differs-from-sql-server
Hao
+8  A: 

You can use logarithms/exponents that take advantage of the mathematical fact that:

log(a*b*c...*n)=log(a)+log(b)+log(c)...+log(n)

Therefore you can use the sum function to add all the logarithms of a column, then take the exponent of that sum, which gives the aggregate multiplication of that column:

create table #tbl (val int)
insert into #tbl (val) values(1)
insert into #tbl (val) values(2)
insert into #tbl (val) values(3)
insert into #tbl (val) values(4)

select exp(sum(log(val))) from #tbl

drop table #tbl

If memory serves me right, there an edge case that needs to be taken care of... log(0) is an error.

spender
+1 for Smooth. Too bad you'd have to wrap it to check for zeros and having to look at rounding/casting/overflow issues and potentially introducing precision errors in going to float.
Cade Roux
+1 I wish I could up vote twice...
Remus Rusanu
was just editing to add the log(0) caveat
spender
great solution . I am very impressed.. this is the one i am looking for
priyanka.sarkar
note that for large sets this method will introduce rounding errors.you might try the clr solution instead. it is slower but accurate.i compared the 2 here:http://weblogs.sqlteam.com/mladenp/archive/2007/02/12/60088.aspx
Mladen Prajdic