The method in the article is only good for positive (>0) values. Here is a formula that works for zeroes and negatives as well:

COALESCE(EXP(SUM(LOG(ABS(NULLIF(val, 0))))),0) * (1 - 2 * (COUNT(CASE WHEN val < 0 THEN 1 END) % 2))

The first part uses NULLIF to replace 0 with NULL, ABS to change negative values to positive ones, then pours that into the log-based product formula (except that this one uses natural logarithms instead of 10-based logarithms). Because of NULL propagation, a single 0 in the input set (which is converted to NULL by NULLIF) will cause the result to be NULL; the COALESCE function fixes this by replacing NULL with 0. The result of this first part is the product of the absolute value of all numbers.

The second part of the formula counts the number of negative values in the input set, then calculates the remainder after division by 2 - resulting in 0 for an even number of negatives, and 1 for an odd number. This value is multiplied by 2 and subtracted from 1, resulting in 1 if there's an even number of negatives, and -1 if there's an odd number. Multiply this by the product of the absolute values to get the final answer.

I first saw this technique in a book by Itzik Ben-Gan (who else?). I now had to google for it, and found my first hit in a comment by Rob Farley to this blog post by Michael Coles - though I did change and simplify Rob's version a bit - he had omitted the COALESCE and instead used a count of the number of zero values - a technique that I don't understand and that, frankly, probably doesn't work.

One problem with this approach is that there is no way to distinguish a NULL that was introduced by the ISNULL (and hence was a 0 at first) from a NULL that was already NULL in the input set, so the result will be 0 if one of the input values is NULL, whereas the proper result would be either NULL (if you want to include the NULL input in the calculation), or the product of the non-NULL values (if you want the aggregate to work like all other aggregates do). I guess the best way to fix this would be to exclude NULL values in the WHERE clause, or to add some extra CASE expressions.

I als found a more readable version that uses the same technique, but broken down into steps, in a reply to this post on stackoverflow. This reply was posted by gbn.

SELECT

GrpID,

CASE

WHEN MinVal = 0 THEN 0

WHEN Neg % 2 = 1 THEN -1 * EXP(ABSMult)

ELSE EXP(ABSMult)

END

FROM

(

SELECT

GrpID,

--log of +ve row values

SUM(LOG(ABS(NULLIF(Value, 0)))) AS ABSMult,

--count of -ve values. Even = +ve result.

SUM(SIGN(CASE WHEN Value < 0 THEN 1 ELSE 0 END)) AS Neg,

--anything * zero = zero

MIN(ABS(Value)) AS MinVal

FROM

Mytable

GROUP BY

GrpID

) foo

This version is more readable, but has the same problem with NULL versions as the compact version above.