# How to multiply rows for a column that contains negative and zero values?

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

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?

The magic of NULLIF 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 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) (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.

• Great answer~ One quick clarification - instead of `EXP(SUM(LOG(...`, this should be `EXP(SUM(LN(...`, correct? `LOG` calculates a Base-10 Logarithm instead of `LN`, which calculates the natural log. May 6 '20 at 3:13
• @chrisdurheim `LN` is not a valid function in SQL Server. Please refer to the docs. May 6 '20 at 12:10
• gotcha; I was working on Postgres; that explains the difference. Thank you! May 8 '20 at 2:19