bluetoft imported from SE
sql-server sql-server-2014
I'm trying to get the Product of all rows for a specific column in a grouped by query.  Most examples I've found point me towards combining `exp`, `sum` and `log`

    exp(sum(log([Column A])))

The problem I'm having is that the column contains some zeros for values and thus I'm getting this error when zeros get passed to the `log` function:
>An invalid floating point operation occurred.

I thought I could work around this by using a `case` expression, but that just doesn't work the way I would think it should, as it seems to evaluate all cases...

      Product = case 
        when min([Value]) = 0 then 0 
        when min([Value]) <> 0 then exp(sum(log(I))) -- trying to get the product of all rows in this column
     from ids
     group by Name


Given the following result set:

    Id  Name  Value
    1   a     1
    2   a     2
    3   b     0
    4   b     1

I would expect to get the following rows:

    Name  Product
    a     2
    b     0

So in summary...
How do you multiply rows in a column that can contain negative or zero valued numbers?
Top Answer
Erik Darling
The magic of [NULLIF][1] seems to do the trick for the test case in your question. Since you used a different example than in your SQL Fiddle, I don't know if that's what you want there too.

    CREATE TABLE dbo.Ids
        Id INT NOT NULL IDENTITY(1, 1),
        Value INT,
        Name NVARCHAR(3)
    INSERT INTO dbo.Ids ( Name, Value )
    VALUES ( 'a', 1 );
    INSERT INTO dbo.Ids ( Name, Value )
    VALUES ( 'a', 2 );
    INSERT INTO dbo.Ids ( Name, Value )
    VALUES ( 'b', 0 );
    INSERT INTO dbo.Ids ( Name, Value )
    VALUES ( 'b', 1 );
    SELECT   Name,
             CASE WHEN MIN(Value) = 0 THEN 0
                  WHEN MIN(Value) > 0 THEN EXP(SUM(LOG(NULLIF(Value, 0)))) -- trying to get the product of all rows in this column
             END AS Product
    FROM     Ids
    GROUP BY Name;


    Name	Product
    a	    2
    b	    0


If you need a more general solution that handles negative numbers and other edge cases, see for example [The Product Aggregate in T-SQL Versus the CLR][2] by Scott Burkow. One T-SQL construction from that article is:

    EXP(SUM(LOG(NULLIF(ABS([Value]), 0))))
    IIF(SUM(IIF([Value] = 0, 1, NULL)) > 0, 0, 1)
    IIF(SUM(IIF([Value] < 0, 1, 0)) % 2 = 1, -1, 1)


As to why your original `CASE` expression did not work as expected, from the documentation for [CASE (Transact-SQL)][3] (emphasis added):

>You should only depend on order of evaluation of the WHEN conditions for scalar expressions (including non-correlated sub-queries that return scalars), **not for aggregate expressions**.

  [1]: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/nullif-transact-sql
  [2]: https://www.codeproject.com/Articles/548395/The-Product-Aggregate-in-T-SQL-Versus-the-CLR
  [3]: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql#remarks
How to multiply rows for a column that contains negative and zero values?

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.