sql-server sql-server-2014 add tag
bluetoft (imported from SE)
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...

    select 
      Name,
      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
      end
     from ids
     group by Name


[SqlFiddle](http://sqlfiddle.com/#!6/5e18f/1)

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
meme (imported from SE)
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;

Returns:

    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

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.