##
SQL Quiz – How To Multiply across Rows
*February 22, 2012*

*Posted by mwidlake in SQL.*

Tags: SQL

17 comments

Tags: SQL

17 comments

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.