Aggregate functions return a single result row based on a group of rows. This differentiates them 

Aggregate functions return a single result row based on a 

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 forMultiplication. 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: 24

Everything 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: -24

So, 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.

MERGE Statment

Use the MERGE statement to select rows from one or more sources for conditionally update or insertion into a table or view. 

CREATE TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100);

INSERT INTO bonuses(employee_id)
   (SELECT e.employee_id FROM employees e, orders o
   WHERE e.employee_id = o.sales_rep_id
   GROUP BY e.employee_id); 

SELECT * FROM bonuses;

EMPLOYEE_ID      BONUS
----------- ----------
        153        100
        154        100
        155        100
        156        100
        158        100
        159        100
        160        100
        161        100
        163        100

MERGE INTO bonuses D
   USING (SELECT employee_id, salary, department_id FROM employees
   WHERE department_id = 80) S
   ON (D.employee_id = S.employee_id)
   WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
     DELETE WHERE (S.salary > 8000)
   WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
     VALUES (S.employee_id, S.salary*0.1)
     WHERE (S.salary <= 8000);

EMPLOYEE_ID      BONUS
----------- ----------
        153        180
        154        175
        155        170
        159        180
        160        175
        161        170
        179        620
        173        610
        165        680
        166        640
        164        720
        172        730
        167        620
        171        740