Calculate sum of digits

Question

I have a column that contains numbers:

Value
-----
123
452
021
111
...

I want to get the sum of each value's digits.

Answer should look like

Value  SumValue
-----  --------
123    6
452    11
021    3
111    3
...    ...  

How can I do this?

I have tried this:

select value
     , case
           when len(value) = 2 then convert(int, left(value, 1))
                                  + convert(int, right(value, 1))
           when len(value) = 3 then convert(int, substring(convert(varchar(1), value), 1,1))
                                  + convert(int, substring(convert(varchar(1), value), 2,1))
                                  + convert(int, substring(convert(varchar(1), value), 3,1))
           else value
       end as SumNum
from get_number (000, 999);

When I get to three digits, it does not calculate correctly, and it seems to me like there's got to be a better solution.

Answer 1

Here's a solution without having to convert the value to strings.

DECLARE @ValueTable TABLE (Value INT)

INSERT INTO @ValueTable (Value)
VALUES
    (5),
    (57),
    (3124),
    (645),
    (312114),
    (9000092)

SELECT
    OriginalValue = V.Value,
    SumOfDigits = 
        V.Value % POWER(10, 1) / POWER(10, 0)
        + V.Value % POWER(10, 2) / POWER(10, 1)
        + V.Value % POWER(10, 3) / POWER(10, 2)
        + V.Value % POWER(10, 4) / POWER(10, 3)
        + V.Value % POWER(10, 5) / POWER(10, 4)
        + V.Value % POWER(10, 6) / POWER(10, 5)
        + V.Value % POWER(10, 7) / POWER(10, 6)
        + V.Value % POWER(10, 8) / POWER(10, 7)
FROM
    @ValueTable AS V

Result:

OriginalValue   SumOfDigits
5               5
57              12
3124            10
645             15
312114          12
9000092         20

I'll explain how this works with an example, like value 645. To get the last digit we calculate the rest of the original value when divided by 10 (this is the mod operator %). Since 645 / 10 is 64.5 (it won't show the decimal value if done on SQL Server because it's treated as INT), the rest is 5.

SELECT 645 % 10 -- Result: 5

To get the second to last digit, we calculate the rest of the original value when divided by 100 (100 = 10 * 10 = 10 ^ 2 = POWER(10, 2)). Since the rest of a division by 100 might result in 2 digits (from 0 to 99) and we just need the tens digit, we divide this result by 10. The result of the division will be INT if the original number was INT and this is exactly what we wanted.

SELECT 
    645 % 100,      -- Result: 45
    645 % 100 / 10  -- Result: 4 (second to last digit)

To get the 3rd to last, we just add an additional 0 (multiply by 10) to both the mod operator value and the division:

SELECT 
    645 % 1000,         -- Result: 645
    645 % 1000 / 100    -- Result: 6 (third to last digit)

This can also be written with the POWER function, which is the expression wrote in the solution.

SELECT 
    645 % POWER(10, 3),                 -- Result: 645
    645 % POWER(10, 3) / POWER(10, 2)   -- Result: 6 (third to last digit)

One thing to note in this solution is that you must add as many additions (each with it's own % and /) as you might have digits on the supplied values.

Also, if you want to repeat this process if the resulting number has more than one digit (for example value 993 -> 21 -> 3), there's a very simple and curious algorithm for this that's called the digital root.

---

Here's a more expressive solution from Andryi M:

SELECT
    OriginalValue = V.Value,
    SumOfDigits = 
          V.Value            % 10
        + V.Value / 10       % 10
        + V.Value / 100      % 10
        + V.Value / 1000     % 10
        + V.Value / 10000    % 10
        + V.Value / 100000   % 10
        + V.Value / 1000000  % 10
        + V.Value / 10000000 % 10
FROM
    @ValueTable AS V