CODEWITHSUNDEEP
sqloracle-database
Giulio Angioli
Giulio Angioli

Reputation: 101

How to query the sum of all digits in a given number?

Can I query the sum of all digits in a given number value?

Example of the input value:

  EMPNO 
  -----  
  7369

I want to get this as a result:

 7+3+6+9 = 25

I thought about this solution, but I don't think it's efficient: 

SELECT SUBSTR('12345',1,1) + SUBSTR('12345',2,1)
FROM DUAL;

Upvotes: 6

Views: 2198

Answers (4)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

One more approach, using XMLTABLE

WITH t (s)
AS (
    SELECT REGEXP_REPLACE(7369, '(\d)', ',\1') 
    FROM DUAL
    )
SELECT sum(to_number(column_value))
FROM t
    ,xmltable(0||s)

Demo

Or, a pure PL/SQL based solution using mod operation.

DECLARE 
    n INTEGER := 2562337328382; 
    s INTEGER := 0; 
BEGIN 
    WHILE n > 0 LOOP 
        s := s + MOD(n, 10); 
        n := floor(n / 10); 
    END LOOP; 
    dbms_output.put_line(s); 
END; 
/ 
54

Upvotes: 1

Arulraj
Arulraj

Reputation: 423

Query for dynamic values.

Declare @Num varchar(max)='54666', @Len int=1,@Total int=0
while @Len<=LEN(@Num)
begin
Set @Total=@Total+CONVERT(int,substring(@Num,@Len,1))
Set @Len=@len+1
end
select @Total

Just change the @Num value and try.

Upvotes: 0

EzLo
EzLo

Reputation: 14189

I'm not fluent in oracle SQL, but this is an approach without converting to string (which will be faster). Basically do a mod + division operation to get each different number, and add them.

DECLARE testNumber integer := 123

SELECT
    testNumber,
    MOD(testNumber, POWER(10, 1)) +
        MOD(testNumber, POWER(10, 2)) / POWER(10, 1) +
        MOD(testNumber, POWER(10, 3)) / POWER(10, 2) +
        MOD(testNumber, POWER(10, 4)) / POWER(10, 3) +
        MOD(testNumber, POWER(10, 5)) / POWER(10, 4) +
        MOD(testNumber, POWER(10, 6)) / POWER(10, 5) +
        MOD(testNumber, POWER(10, 7)) / POWER(10, 6)
FROM
    DUAL

Will have to keep add sums up to the max amount of positions you might get.

Upvotes: 3

user8406805
user8406805

Reputation:

Here is the solution:

1. Simple Query

SELECT id,sum(SUBSTR(empno, LEVEL, 1 )) sums
FROM   (select 1 as id, 7369 as empno from DUAL)
      CONNECT BY LEVEL <= LENGTH(empno)
group by id

2. Using CROSS JOIN

SELECT sum(column_value)
FROM   (
  SELECT 7369 val,
         ROWNUM AS id
  FROM dual
) t
CROSS JOIN
TABLE(
  CAST(
    MULTISET(
      SELECT SUBSTR( t.val, LEVEL, 1 )
      FROM   DUAL
      CONNECT BY LEVEL <= LENGTH( t.val )
    )
    AS SYS.ODCIVARCHAR2LIST
  )
) c

Output:

SUM(COLUMN_VALUE)
25

Upvotes: 3

Related Questions