Re: Instead of SUM() I require MULTIPLY

Jonathan, your soution is pretty clever, and actually more useful than you seem to think.

The shortcomings you mentioned aren't insurmountable.

What you need to do is:

• SUM the logarithms of absolute values of non-negative numbers and take the exponent. This will be the MAGNITUDE of your result. EXP(SUM(case when x > 0 then ln(x) when x < 0 then ln(-x) else NULL end))
• COUNT the negative values. (Their parity will determine the SIGN of your result.) COUNT(case when x < 0 then 1 else null end)
• COUNT the zero values (If the count is non-zero, then your final result is ZERO) COUNT(case when x = 0 then 1 else null end)

 From these, computing the final product is easy -- I won't waste bits on the details.

Sadly, there remains one minor shortcoming that is not so easy to fix. The final result is only APPROXIMATE! We are performing some fairly complex floating-point operations to approximate what should (or a least *could*) be an integer computation. For example, when I loaded up a test table with the values 2, 2, 2, 3, -1, -1, -1, 0, -1 I came up with these results:

MAGNITUDE:   24.00000000000000000000000000000000000005
NEGATIVES:     3
ZEROES:          1

Not bad really -- there is definitely a rounding error, but it's pretty minor. I suspect this error will increase according to something like the number of distinct values in the table; it could actually become significant over a sufficiently large amount of data. Happily, 39 signficant (decimal) digits go a *long* way; this is not nearly so awful as doing binary floating-point operations with (something like) a 23-bit mantissa...

Jonathan Gennick wrote:

>A caveat to my previous response. That trick works only for positive
>numbers. It'll fail if a zero or negative value is encountered.
>
>Best regards,
>
>Jonathan Gennick --- Brighten the corner where you are
>http://Gennick.com * 906.387.1698 * mailto:jonathan@gennick.com
>
>
>
>Friday, December 9, 2005, 5:13:09 PM, Ethan Post (post.ethan_at_gmail.com) wrote:
>EP> I just came up with a function I would like, but don't think exists.
>
>EP> TABLE TEST (X NUMBER)
>EP> =====================
>EP> 2
>EP> 2
>EP> 4
>
>EP> select sum(x) from test;
>
>EP> will return 8...
>
>EP> what I need is
>
>EP> select multiply(x) from test;
>
>EP> will return 16, because 2*2*4 is 16.
>
>EP> Anyone ever seen a SQL aggregate function like this? I don't think it exists
>EP> but I hold out hope.
>
>EP> - Ethan
>
>--
>http://www.freelists.org/webpage/oracle-l
>
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l