# Calculate sum of digits

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.

## 1 Answer

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
``````