This discussion is archived
9 Replies Latest reply: Mar 11, 2013 3:17 AM by dilipkumar10285 RSS

function for cross sum of numeric value

user447000 Newbie
Currently Being Moderated
Hi,

I want to calculate the cross sum of a numeric value.
Example: "cross sum" of 567 = 5+6+7 = 18

I need something like:
SELECT SUM(cross_sum(document_number)) FROM invoice;

Is there any solution out of the box (Oracle 11g) or must I write my own function?

Matt
  • 1. Re: function for cross sum of numeric value
    ranit B Expert
    Currently Being Moderated
    ranit@XE11GR2>> select SUM(x) from(
      2  select substr(568,level,1) x from dual
      3  connect by level <= length(568)
      4  );
    
        SUM(X)
    ----------
            19
  • 2. Re: function for cross sum of numeric value
    chris227 Guru
    Currently Being Moderated
    Dont know any, however
    with data as (
    select 24223 n from dual
    )
    
    select
     sum(to_number(substr(abs(n), level, 1))) s
    from data
    connect by
    level <= length(n)
    S
    "13"
    Edited by: chris227 on 08.03.2013 06:39
    extended for negativ numbers
  • 3. Re: function for cross sum of numeric value
    user447000 Newbie
    Currently Being Moderated
    I'm not familiar with this "connect by".

    Just to make my example more complete:

    CREATE TABLE foo_invoice
    (
    documentnumber NUMBER(4)
    );

    INSERT INTO foo_invoice
    (documentnumber ) VALUES (111);

    INSERT INTO foo_invoice
    (documentnumber ) VALUES (222);

    Now I want to add up the "cross sums". So expect a result of (1+1+1)+ (2+2+2) = 9.

    This is why I wrote in my first post:
    "SELECT SUM(cross_sum(documentnumber)) FROM foo_invoice".

    In my real table I have about 100 million records. So I nees a performant solution.
  • 4. Re: function for cross sum of numeric value
    chris227 Guru
    Currently Being Moderated
    Ok, in this case connect by may be not the best approach.
    As there are only at most 4 digits, did you try something like
      1  with data as (
      2  select 123 n from dual
      3  union all
      4  select null from dual
      5  )
      6  select
      7     nvl(to_number(substr(n,1,1)),0)
      8   +nvl(to_number(substr(n,2,1)),0)
      9   +nvl(to_number(substr(n,3,1)),0)
     10   +nvl(to_number(substr(n,4,1)),0)
     11   s
     12* from data
    SQL> /
    
             S
    ----------
             6
             0
    Do you want touse it in a view or update a column?
    What's your DB-Version?

    Ok, i see you changed your requirements.
    Now you want the digit sum over all rows
      1  with data as (
      2  select 123 n from dual
      3  union all
      4  select 456 from dual
      5  union all
      6  select null from dual
      7  )
      8  select
      9  sum(
     10     nvl(to_number(substr(n,1,1)),0)
     11   +nvl(to_number(substr(n,2,1)),0)
     12   +nvl(to_number(substr(n,3,1)),0)
     13   +nvl(to_number(substr(n,4,1)),0)
     14  )
     15   s
     16* from data
    SQL> /
    
             S
    ----------
            21
    
    also
    
    select
      sum(
         nvl(floor(abs(n)/1000),0) 
        +nvl(floor(mod(abs(n)/100, 10)),0)
        +nvl(floor(mod(abs(n)/10, 10)),0)
        +nvl(mod(abs(n),10),0)
    )
    from
    data
    If you want also do a group by you should in fact create a user defined aggregate function.
    Blue shadow gave e plenty of example in this forum already for this.

    Edited by: chris227 on 08.03.2013 07:32

    Edited by: chris227 on 08.03.2013 07:57
  • 5. Re: function for cross sum of numeric value
    Solomon Yakobson Guru
    Currently Being Moderated
    user447000 wrote:
    Now I want to add up the "cross sums". So expect a result of (1+1+1)+ (2+2+2) = 9.
    select  sum(xmlcast(xmlquery(regexp_replace('0' || documentnumber,'(\d)','+\1') returning content) as number)) sum_of_digits
      from  foo_invoice
    /
    
    SUM_OF_DIGITS
    -------------
                9
    
    SQL> 
    SY.
  • 6. Re: function for cross sum of numeric value
    odie_63 Guru
    Currently Being Moderated
    Solomon Yakobson wrote:
    select  sum(xmlcast(xmlquery(regexp_replace('0' || documentnumber,'(\d)','+\1') returning content) as number)) sum_of_digits
    from  foo_invoice
    /
    Solomon,

    I wouldn't try that one on 100 millions rows ;)

    As far as XQuery is concerned, the following is more scalable (no functional evaluation involved) :
    select sum(
             xmlcast(
               xmlquery(
                 'sum(for $i in 1 to string-length($n) 
                      return xs:integer(substring($n,$i,1)))'
                 passing to_char(documentnumber) as "n"
                 returning content
               ) as number
             )
           )
    from  foo_invoice ;
  • 7. Re: function for cross sum of numeric value
    Solomon Yakobson Guru
    Currently Being Moderated
    odie_63 wrote:
    I wouldn't try that one on 100 millions rows ;)
    No matter what OP does, splitting 100 million numbers into digits and summing them up will not be fast. But you're right, OP should stay away from xml (and hierarchical queries too). I'd stick with chris227 solution using mod/floor.

    SY.
  • 8. Re: function for cross sum of numeric value
    user447000 Newbie
    Currently Being Moderated
    Hi,

    after all I wrote a small helper function based on

    ...
    PARALLEL_ENABLE
    ...
    BEGIN
    vLength := LENGTH(pRENr);
    rc := 0;
    FOR i IN 1..vLength LOOP
    rc := rc + nvl(to_number(substr(pRENr,i,1)),0);
    END LOOP;
    RETURN rc;
    END;

    BTW pRENr is NUMBER(16).

    Performance was quite good. For 66 millions RENRs
    a
    SELECT /*+ PARALLEL(r 32) */ SUM(getCrossSum( i.RENR))
    FROM invoice i;

    took 20 seconds which is acceptable.
  • 9. Re: function for cross sum of numeric value
    dilipkumar10285 Explorer
    Currently Being Moderated
    Hi,

    Hope this will help you.
    with data as (
         select 123 n from dual
         union all
         select 22 from dual
         union all
         select 44 from dual
         union all
         select 123 from dual
    )
    select sum(row_total) col_total from 
    (select n,
              (select  sum(REGEXP_SUBSTR(n, '[[:digit:]]', 1, level)) from dual connect by level <= length(n)) row_total
    from data);
    OR
    with data as (
         select 123 n from dual
         union all
         select 22 from dual
         union all
         select 44 from dual
         union all
         select 123 from dual
    )
    select sum(row_total) col_total from
    (select n,
              (select sum(nvl(to_number(substr(n,level,1)),0)) from dual connect by level <= length(n)) row_total
    from data);
     COL_TOTAL
    ----------
            24
    Cheers
    Dilipkumar

    Edited by: dilipkumar vishwakarma on Mar 11, 2013 3:47 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points