Clever Geek Handbook

Performing Multiplication in SQL SERVER (SET BASED APPROACH)

Suppose I have a table like the following:

tblNumbers

Numbers
4  
5
3
6

Using the SET BASED approach, how can I perform the multiplication so that the output is:

Output
360

NB There is no hard and fast rule that there will be only four numbers, but I would prefer the answer to use a CTE and / or a correlated subquery.

+2


source to share


3 answers


You can use logarithms / exponents that use the mathematical fact that:

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

Therefore, you can use a function sum

to add all the logarithms of a column, and 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 suits me, there is an edge case to be taken care of ... log (0) is an error.

+9


source 


declare @result int
set @result = 1

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

print @result


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

+2


source


The Michael effect is effective.

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

0


source






More articles:


All Articles