Performing multiplication in SQL SERVER(SET BASED APPROACH)
sql, sql-server, sql-server-2005, t-sql
Solution
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.
Problem
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.