How to get the productsum from a table

Wed Nov 19, 2008 by Peter Larsson in sql-server-2008, algorithms, sql-server-2005, sql-server-2000

IF EXISTS(SELECT * FROM YourTable WHERE Number = 0)
    SELECT 0.0E
ELSE
    SELECT CASE IsNegativeProduct
               WHEN 1 THEN -EXP(theSum)
               ELSE EXP(theSum)
           END
    FROM   (
               SELECT SUM(LOG(ABS(Number))) AS theSum,
                      SUM(CASE WHEN Number < 0 THEN 1 ELSE 0 END) % 2 AS IsNegativeProduct
               FROM   YourTable
           ) AS d