Sum of Digits of a Number in SQL Server Without Using Traditional Loops Like While

Sum of digits of a number in sql server without using traditional loops like while

It's a fun exercise if a bit pointless.

DECLARE @var1 int   = 12345;

WITH i AS (
    SELECT @var1 / 10 n, @var1 % 10 d

    UNION ALL

    SELECT n / 10, n % 10
    FROM i
    WHERE n > 0
)
SELECT SUM(d)
FROM i;

Sum the digits of a number in mysql

I wanted to test this using Fiddle or Rextester, but neither is working right now. So, your upvotes/downvotes will serve as the test:

SELECT CAST(SUBSTRING(number, 1, 1) AS UNSIGNED) +              -- first digit
       CAST(SUBSTRING(number, 2, 1) AS UNSIGNED) +              -- second digit
       CAST(SUBSTRING(number, 3, 1) AS UNSIGNED) AS the_sum     -- third digit
FROM yourTable

This assumes a number with a max width of 3 digits, which is also zero padded (as you mentioned we can assume).

If you really need to do this in production, you should probably create a user defined function to handle such manipulations, edge cases, etc.

Update:

Going with the comment by @ThorstenKettner we could generalize this answer to a number field of any length by just adding more terms for each digit position. For example, if we wanted to cover numbers which could be up to four digits wide we could just add this term:

+ CAST(SUBSTRING(number, 4, 1) AS UNSIGNED)

which would either add a number if present, or would add zero if not present.

Reverse a number and sum of digits in sql

For Postgres you can use string_to_array() to split the number into digits:

with data (a) as (
  values
    (34),
    (123),
    (345),
    (879)
)
select a, 
       string_agg(t.d::text, '' order by t.idx desc) as b, 
       sum(t.d::int) as c
from data, 
     unnest(string_to_array(a::text,null)) with ordinality as t(d, idx)
group by a;

The above returns:

a   | b   | c 
----+-----+---
 34 | 43  |  7
123 | 321 |  6
345 | 543 | 12
879 | 978 | 24

To get the reversed number, you could also use reverse() in Postgres

Sum of digits program, Getting wrong answer

Only have to do one change in the code , i.e. use the operator \ instead of /
when you reduce your number....

i.e. instead of n = CInt(n/10) use n = n\10

Therefore the code will work fine as butter:

<%
dim n,sum,d
sum = 0
n = request.querystring("n")    'taking value from querystring

while n <> 0
d = n mod 10
sum = sum +d
n = n\10                       'using \ for integer division
wend
response.write("Sum of digits of : "&request.querystring("n")&" is = "&sum)
%>

For those who dont understand what just happened see below:

1. Operator / : Performs a floating division.
2. Operator \ : Performs a Integer division.

Thats why I was not getting correct results.
Hope this helps!

---

Related Topics