1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Write a query to display sum of all digits of a given number upto single digit

Discussion in 'SQL PL/SQL' started by jagadekara, May 28, 2014.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Code (SQL):
    CREATE OR REPLACE FUNCTION XXST_SUM_OF_DIGITS(p_num NUMBER)  RETURN NUMBER IS
      a NUMBER(12);
      b NUMBER;
      c NUMBER;
      d NUMBER;
      e NUMBER;
    BEGIN
        a:=P_NUM;
        b:= nvl(substr(a,1,1),0)+nvl(substr(a,2,1),0)+nvl(substr(a,3,1),0)+nvl(substr(a,4,1),0)
            +nvl(substr(a,5,1),0)+nvl(substr(a,6,1),0)+nvl(substr(a,7,1),0)+nvl(substr(a,8,1),0)
            +nvl(substr(a,9,1),0)+nvl(substr(a,10,1),0)+nvl(substr(a,11,1),0)+nvl(substr(a,12,1),0);
        c:= nvl(substr(b,1,1),0)+nvl(substr(b,2,1),0)+nvl(substr(b,3,1),0);
        d:= nvl(substr(c,1,1),0)+nvl(substr(c,2,1),0)+nvl(substr(c,3,1),0);
        e:= nvl(substr(d,1,1),0)+nvl(substr(d,2,1),0)+nvl(substr(d,3,1),0);
      RETURN(e);
    END;

    SELECT XXST_SUM_OF_DIGITS(123456789012) FROM dual;

    Ans: 3
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
  3. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
  4. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi,

    I can see it when I click on the link - but I think you (or any body else) can't see it because it is in status "Not Published".

    Anyway, here's the code in the meantime :

    Code (SQL):
    CREATE OR REPLACE
      FUNCTION SF_SUM_OF_DIGITS(
          p_num NUMBER)
        RETURN NUMBER
      IS
        l_sum NUMBER;
        l_len NUMBER := LENGTH(p_num);
        l_num NUMBER;
      BEGIN
        l_num  := p_num;
        l_sum  := p_num;
        WHILE (l_len > 1)
        LOOP
          l_sum := 0;
          FOR i IN 1 .. l_len
          LOOP
            l_sum := l_sum + SUBSTR(l_num, i, 1);
          END LOOP;
          l_len := LENGTH(l_sum);
          l_num := l_sum;
        END LOOP;
        RETURN(l_sum);
      END;
      /
     
    SELECT SF_SUM_OF_DIGITS(123456789012) FROM dual;
     
     
    SF_SUM_OF_DIGITS(123456789012)
    ------------------------------
                                 3
     
    SELECT sf_sum_of_digits(1234) FROM dual;
     
    SF_SUM_OF_DIGITS(1234)
    ----------------------
                         1
     
    SELECT sf_sum_of_digits(5) FROM dual;
     
    SF_SUM_OF_DIGITS(5)
    -------------------
                      5
     
     
  5. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Nice... :cool:
     
  6. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    It was pending moderation... It is now Live.
     
  7. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Thanks Sadik.