Aggregate functions return a single result row based on a group of rows. This differentiates them from Single-Row functions which act on each row. These functions are extensively used with the GROUP BY clause in SQL statements. AVG (), COUNT (), SUM () … are few aggregate functions which are quite commonly used. Today, one of my colleague asked me if there is some aggregation function for Multiplication. I thought about it for a while and found myself surprised that I have never thought about doing such a thing
So, How do we do the multiplication then? I tried it but just couldn’t do it in SQL. So, I asked this question on our internal self help channel and I got a pretty impressive reply:
“Using a mathematical approach…”
After understanding the solution, I was surprisingly happy with the simplicity of the approach and found it worth sharing. Let’s assume that we have a table “tbl” with one column “num”. This table has three rows having values 2, 3 & 4 for column “num”.
WITH tbl AS
(SELECT 2 num
FROM DUAL
UNION
SELECT 3 num
FROM DUAL
UNION
SELECT 4 num
FROM DUAL)
SELECT num
FROM tbl;
We need the multiplication of row’s data for this column. So essentially, we are looking for an aggregate function MUL (num).There is no such function as MUL () in Oracle (I actually tried using it). Here comes the computational part of the puzzle. A multiplication operation can be mathematically expressed as:
MUL (num) = EXP (SUM (LN (num)))
Not very clear at first, I agree. Lets review the maths behind it:
x = (2 * 3 * 4)
ln(x) = ln(2 * 3 * 4)
ln(x) = ln(2) + ln(3) + ln(4) => SUM(LN(num))
ln(x) = .693 + 1.098 + 1.386
ln(x) = 3.178
x = e (3.178) => EXP(SUM(LN(num)))
x = 24
And that’s it. We just created our own multiplication function and now the result can be calculated as:
WITH tbl AS
(SELECT 2 num
FROM DUAL
UNION
SELECT 3 num
FROM DUAL
UNION
SELECT 4 num
FROM DUAL)
SELECT EXP (SUM (LN (num))) MUL
FROM tbl;
Result: 24Everything looks perfect. But hey, I have got negative values. The moment you put a negative value in the dataset, you are bound to get the following Oracle error:
“ORA-01428: argument ‘x’ is out of range”
This is because the range for LN () argument is > 0. But this is now easy to handle, here is how:
WITH tbl AS
(SELECT -2 num
FROM DUAL
UNION
SELECT -3 num
FROM DUAL
UNION
SELECT -4 num
FROM DUAL),
sign_val AS
(SELECT CASE MOD (COUNT (*), 2)
WHEN 0 THEN 1
ELSE -1
END val
FROM tbl
WHERE num < 0)
SELECT EXP (SUM (LN (ABS (num)))) * val
FROM tbl, sign_val
GROUP BY val
Result: -24So, we first counted the negative records in the table. If the count is odd, the final result should be negative and vice versa. We then multiplied this signed value with the multiplication of the absolute values. A subquery can also be used instead of GROUP BY but that’s trivial. Now the solution is complete and we are able to handle the negative values too.
I was so impressed by this approach that I haven’t given a thought about any other solution. But I am sure there would be. If you find a different approach, please share.
