CODEWITHSUNDEEP
mysql
Lamar
Lamar

Reputation: 1849

Sum the digits of a number in mysql

I have a query that returns an integer number from a mathematical calculation. I need to sum all the digits in that integer number.

Something like this:

select sumdigits(number) from dual
-- if number =123, output: 1+2+3 = 6 
-- if number =100, output: 1+0+0 = 1

Upvotes: 2

Views: 2242

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521168

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.

Upvotes: 4

Álvaro González
Álvaro González

Reputation: 146440

I'm ashamed to even suggest this but...

SELECT
    foo,
    CHAR_LENGTH(
        REPLACE(
            REPLACE(
                REPLACE(
                    REPLACE(
                        REPLACE(
                            REPLACE(
                                REPLACE(
                                    REPLACE(
                                        REPLACE(
                                            REPLACE(
                                                REPLACE(foo, '-', ''),
                                            '0', ''),
                                        '1', '1'),
                                    '2', '22'),
                                '3', '333'),
                            '4', '4444'),
                        '5', '55555'),
                    '6', '666666'),
                '7', '7777777'),
            '8', '88888888'),
        '9', '999999999')
    ) AS digit_sum
FROM (
     SELECT 123 AS foo
     UNION ALL SELECT 100
     UNION ALL SELECT 413432143
     UNION ALL SELECT -6301
     UNION ALL SELECT 1234567890
) x

+------------+-----------+
| foo        | digit_sum |
+------------+-----------+
|        123 |         6 |
|        100 |         1 |
|  413432143 |        25 |
|      -6301 |        10 |
| 1234567890 |        45 |
+------------+-----------+
5 rows in set (0.00 sec)

It probably makes more sense rewritten as function, together with some error checking to return NULL on floats or something similar.

Upvotes: 3

Related Questions