3 ms·

I was surprised that MySQL lacked a Product() aggregate function with its GROUP BY. After much searching I came to this little nugget: "exp(sum(log(coalesce(t

by physcab 17y ago

I was surprised that MySQL lacked a Product() aggregate function with its GROUP BY. After much searching I came to this little nugget: "exp(sum(log(coalesce(the field you want to multiply,1))) The coalesce() function is there to guard against trying to calculate the logarithm of a null value and may be optional depending on your circumstances." MySQL is full of hacks.

crux_ 17y ago
I'm torn as to whether to call that a 'hack' or not, since it's just a bit of math. [ exp(log(x)) == x; log(ab) == log(a) + log(b) ]
- kogir 17y ago
  It loses precision in scenarios where the desired aggregate would not. It's a hack.
  - crux_ 17y ago
    I'd be more inclined to call it a hack because it probably either crashes or gives the wrong answer when it encounters a zero. ;)
chrisbolt 17y ago
You could always write a patch and send it to them. A product aggregate function probably wouldn't be hard to write.