SQL Quiz – How To Multiply across Rows

A colleague came to me a couple of days ago with a SQL problem. He had something like this:

@get_source

NAME          INPUT
------------- -----
GROUP_1       5
GROUP_2       3
GROUP_3       4
GROUP_4       7
GROUP_5       3

What he wanted to do was multiply all the inputs across the groups, to get the total number of possible permutations. ie 5*3*4*7*3. The product of all the INPUTS. This has to be in straight SQL. Easy! You just need to… Ahhh… Hmmm…

No, I just could not think of a way to do it that my colleague could use.

- There is no group-by function that gives a product of a column {that I know of}
- We could not use PL/SQL {for reasons I won’t go into}, so no cursor looping or passing in an array, which would make it simple
- Neither of us could think of an analytical function that could take the result of itself in the prior row (though I suspect there may be a way to do it).
- The number of groups could and would vary from a few to possibly a hundred, so the old tricks of converting rows to columns or nesting so many sql statements would not help.

So, I asked my friend – the queen of SQL Query, {Boneist} {Oh she of the trombone playing pastime}.

She came straight back with an answer. In case you want to try and work out an answer yourself before seeing the solution, below is a cat picture. The answer she came up with is below that:

The key to the solution is natural logs {ln}. I don’t know about you, but I learnt about using logs at school and have pretty much not used them since. In summary:

If x=3*5*9
then ln(x) = ln(3)+ln(5)+ln(9)
= 1.09861+1.60944+2.19722
= 4.90527

ie using log converts multiplication to addition. You then use EXP, the inverse of ln, to convert your added-up log value into your result.

exp(4.90527) = 135

{NB if you use my figures, exp(4.90527) realy equals 134.999355, as I have truncated the log values shown. Oracle does this far more accurately internally but be aware you might get some slight rounding errors).

So, what we can do is simply use the SQL GROUP function SUM to add together the natural logs of all the rows:

sum(ln(input))
{grouped by the whole statement, so no group by is needed in this case}

As an example:

-- show the expected result first
select 3*7*4*5*1 from dual;

 3*7*4*5*1
----------
       420


select min(name),max(name),count(name)
,EXP (SUM (LN (gr_sum))) gr_prod
from
(select 'group_1' name, 3 gr_sum from dual
 union
 select 'group_2' name, 7 gr_sum from dual
 union
 select 'group_3' name, 4 gr_sum from dual
 union
 select 'group_4' name, 5 gr_sum from dual
 union
 select 'group_5' name, 1 gr_sum from dual
)
/

MIN(NAM MAX(NAM COUNT(NAME)    GR_PROD
------- ------- ----------- ----------
group_1 group_5           5        420

As you can see, it works – even if when you first look at the formula your brains {if you are not a mathematician} try to leak out of your head. Just try and remember what your mathematics teacher said about log books and how, before calculators, they were used to speed up manual long multiplication tasks by converting the task into log addition.

If you want more information on logs, see this discussion about how they are actually about growth or wikipedia if you must .

Boneist actually pointed me to this very nice post about using logs in oracle by Anju Parashar, which I have borrowed from.

One issues to be aware of (which is highlighted in Anuj Parashar’s article) is that you can’t get a log of negative values, as a consequence Oracle will give you an ora-01428 error:

select ln(-3) from dual;
select ln(-3) from dual
*
ERROR at line 1:
ORA-01428: argument ‘-3′ is out of range

Anuj gives a version of code that works if all values are negative, below I have one that copes with any number of negatives. Basically, you convert all the values to be multiplied to positive values and then make it negative if the count of negative values is odd. Mathematically, the result of a multiplication can ONLY be negative if there are an odd number of negative values.

,EXP (SUM (LN (abs(gr_sum))))
*decode (mod(sum(decode(sign(gr_sum),0,0,1,0, 1)),2)
,0,1,-1) correct_gr_prod

I’m sure that the above expression could be simplified, but I have to go and do the day job.

Finally, here is a little set of test cases covering the above, so you can play with this.

mdw1123> select 3*7*4*5*1 from dual;

 3*7*4*5*1
----------
       420

1 row selected.

mdw1123> --
mdw1123> select 'group_1' name, 3 gr_sum from dual
  2  union
  3  select 'group_2' name, 7 gr_sum from dual
  4  union
  5  select 'group_3' name, 4 gr_sum from dual
  6  union
  7  select 'group_4' name, 5 gr_sum from dual
  8  union
  9  select 'group_5' name, 1 gr_sum from dual
 10  /

NAME        GR_SUM
------- ----------
group_1          3
group_2          7
group_3          4
group_4          5
group_5          1

5 rows selected.

mdw1123>
mdw1123> select min(name),max(name),count(name)
  2  ,EXP (SUM (LN (gr_sum))) gr_prod
  3  from
  4  (select 'group_1' name, 3 gr_sum from dual
  5   union
  6   select 'group_2' name, 7 gr_sum from dual
  7   union
  8   select 'group_3' name, 4 gr_sum from dual
  9   union
 10   select 'group_4' name, 5 gr_sum from dual
 11   union
 12   select 'group_5' name, 1 gr_sum from dual
 13  )
 14  /

MIN(NAM MAX(NAM COUNT(NAME)    GR_PROD
------- ------- ----------- ----------
group_1 group_5           5        420

1 row selected.

mdw1123> --
mdw1123> -- now with a negative
mdw1123> select 'group_1' name, 3 gr_sum from dual
  2   union
  3   select 'group_2' name, -7 gr_sum from dual
  4   union
  5   select 'group_3' name, 4 gr_sum from dual
  6   union
  7   select 'group_4' name, 5 gr_sum from dual
  8   union
  9   select 'group_5' name, 1 gr_sum from dual
 10  /

NAME        GR_SUM
------- ----------
group_1          3
group_2         -7
group_3          4
group_4          5
group_5          1

5 rows selected.

mdw1123> -- and if the values contain negatives
mdw1123> select min(name),max(name),count(name)
  2  ,EXP (SUM (LN (abs(gr_sum)))) gr_prod
  3  ,mod(sum(decode(sign(gr_sum),0,0
  4                          ,1,0
  5                          ,  1)
  6           ),2) -- 0 if even number of negatives, else 1
  7           modifier
  8  ,EXP (SUM (LN (abs(gr_sum))))
  9   *decode (mod(sum(decode(sign(gr_sum),0,0,1,0,     1)),2)
 10         ,0,1,-1) correct_gr_prod
 11  from
 12  (select 'group_1' name, 3 gr_sum from dual
 13   union
 14   select 'group_2' name, -7 gr_sum from dual
 15   union
 16   select 'group_3' name, 4 gr_sum from dual
 17   union
 18   select 'group_4' name, 5 gr_sum from dual
 19   union
 20   select 'group_5' name, 1 gr_sum from dual
 21  )
 22  /

MIN(NAM MAX(NAM COUNT(NAME)    GR_PROD   MODIFIER CORRECT_GR_PROD
------- ------- ----------- ---------- ---------- ---------------
group_1 group_5           5        420          1            -420

1 row selected.