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
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?