10

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?

13

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.

| improve this answer | |
  • 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. – chrisdurheim May 6 at 3:13
  • @chrisdurheim LN is not a valid function in SQL Server. Please refer to the docs. – Erik Darling May 6 at 12:10
  • gotcha; I was working on Postgres; that explains the difference. Thank you! – chrisdurheim May 8 at 2:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.