How to Multiply Column Values in SQL Server

We had a recent discussion on the public newsgroups on how to get this requirement in a single statement without using T-SQL. I was indeed taken by surprise how this requirement was answered by fellow MVP's. Infact there is a solution even though no so elegant, but works great having said what the requirement looks. So take the following code and you will find it for yourself.

Create table MyTable (sample int)

 GO

Insert into mytable values (1)

Insert into mytable values (2)

Insert into mytable values (3)

Insert into mytable values (4)

Insert into mytable values (5)

So our example is simple. We need to find the product of the column sample from the above requirement. This though can be achieved with the dubious assignment syntax that still works great as:

DECLARE @prod INTEGER

 SELECT @prod = sample*COALESCE(@prod,1) FROM MyTable

 SELECT @prod

But there are a number of arguements to why this will work or not work. A simple search at the newsgroups will give you the pitfalls. Moreover for people who use this syntax in SQL Server 2000 need to note that this works in SQL Server 2000 Post SP2 only. This did turn to be prety easy. But the actual result that stunned me is:

SELECT CAST(ROUND(

  COALESCE(EXP(SUM(LOG( ABS(NULLIF(sample,0))))),0)

   * SIGN(MIN( ABS(sample)))

   * (COUNT(NULLIF(SIGN(sample),1))%2*-2+1)

  ,0)  AS INTEGER) AS product

 FROM MyTable

From the MVP who posted this solution, I had asked the logic behind this. The answer was "Fundamentally it just exploits the fact that the logarithm of the product is equal to the sum of the logs: LOG(a) + LOG(b) = LOG(a * b). The SIGN(MIN()) and COUNT(*) expressions are there to handle negative values and zeros correctly."

So I do see the potential that SET based solutions can offer. It is quite powerful and even though the solutions might not be straight forward, the solutions do work elagant and flawlessly.