9 Replies Latest reply: Mar 11, 2013 3:17 AM by dilipkumar10285 RSS

    function for cross sum of numeric value

    user447000
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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