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.
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.
declare @result int
set @result = 1
select @result = @result * [number]
from tblNumber
print @result
(note that this assumes an int column and no overflow)
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.