SQL numbers summation numbers

Question

I am using presto. I have an ID field that is numeric. I need a column that adds numbers to the identifier. Therefore, if ID = 1234, I need a column that outputs 10 ie 1 + 2 + 3 + 4.

I could use a substring to extract each digit and sum it, but is there a function I can use or an easier way?

Answer 1

You can combine regexp_extract_all from regexp_extract_all 's answer with lambda support recently added to Presto. This way you do not need unnest . The code would really be clear if it weren't for cast to varchar :

 presto> select reduce( regexp_extract_all(cast(x as varchar), '\d'), -- split into digits array 0, -- initial reduction element (s, x) -> s + cast(x as integer), -- reduction function s -> s -- finalization ) sum_of_digits from (values 1234) t(x); sum_of_digits --------------- 10 (1 row) 

Answer 2

If I read your question correctly, you want to avoid hard-coding a substring for each digit in the identifier, for example substring (ID,1,1) + substring (ID,2,1) + ...substring (ID,n,1) . This is inelegant and only works if all of your ID values ​​are the same length.

Instead, you can use a recursive CTE. Performing this method works for identifier fields with variable length values.

Disclaimer: this still technically uses substring , but it does not make a clumsy capture of hard code

 WITH recur (ID, place, ID_sum) AS ( SELECT ID, 1 , CAST(substring(CAST(ID as varchar),1,1) as int) FROM SO_rbase UNION ALL SELECT ID, place + 1, ID_sum + substring(CAST(ID as varchar),place+1,1) FROM recur WHERE len(ID) >= place + 1 ) SELECT ID, max(ID_SUM) as ID_sum FROM recur GROUP BY ID 

Answer 3

First use REGEXP_EXTRACT_ALL to split the string. Then use CROSS JOIN UNNEST GROUP BY to group the extracted digits by their number and the sum above them.

Here

 WITH my_table AS (SELECT * FROM (VALUES ('12345'), ('42'), ('789')) AS a (num)) SELECT num, SUM(CAST(digit AS BIGINT)) FROM my_table CROSS JOIN UNNEST(REGEXP_EXTRACT_ALL(num,'\d')) AS b (digit) GROUP BY num ;