Row Data Multiplication in Oracle

aggregate function multiplyAggregate 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;
Code language: SQL (Structured Query Language) (sql)
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;
Code language: SQL (Structured Query Language) (sql)
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
Code language: SQL (Structured Query Language) (sql)
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.
Get our Articles via Email. Enter your email address.

You may also like...

6 Comments

  1. Thiago Arrais says:

    Wow! Very simple!

  2. Nina says:

    Very Good Example. Did not think it was so easy

  3. Sergey says:

    It`s not work if any num=0

    • Vishal B says:

      SET SERVEROUTPUT ON;
      DECLARE
      v_str VARCHAR2(100);
      v_num NUMBER;
      BEGIN

      WITH tbl AS
      (SELECT 2 num
      FROM DUAL
      UNION
      SELECT 3 num
      FROM DUAL
      UNION
      SELECT 4 num
      FROM DUAL)
      SELECT LISTAGG(num, ‘*’) WITHIN GROUP (ORDER BY num) INTO v_str
      FROM tbl;

      EXECUTE IMMEDIATE ‘SELECT ‘ ||v_str||’ FROM dual’ INTO v_num;
      DBMS_OUTPUT.PUT_LINE (v_str || ‘ = ‘|| v_num);
      END;

  4. Vishal B says:

    SET SERVEROUTPUT ON;
    DECLARE
    v_str VARCHAR2(100);
    v_num NUMBER;
    BEGIN

    WITH tbl AS
    (SELECT 2 num
    FROM DUAL
    UNION
    SELECT 3 num
    FROM DUAL
    UNION
    SELECT 4 num
    FROM DUAL)
    SELECT LISTAGG(num, ‘*’) WITHIN GROUP (ORDER BY num) INTO v_str
    FROM tbl;

    EXECUTE IMMEDIATE ‘SELECT ‘ ||v_str||’ FROM dual’ INTO v_num;
    DBMS_OUTPUT.PUT_LINE (v_str || ‘ = ‘|| v_num);
    END;

  5. anonymous says:

    good article

Leave a Reply

Your email address will not be published. Required fields are marked *