Count a Numeric Varchar Column Stored in Base Six

Question

I have a table that stores a numeric value in a VARCHAR column, this column is storing values calculated in base six math. Meaning:

3.5 = 3 + 5 / 6

9.4 = 3.4 + 5.6

I need to get the sum of the values in these rows. I know I need to separate them based on id, then add specific ids' base-sixe numbers together, however I don't know where to start. Is there an easy way to do it?

Answer 1

You can convert these numbers to ordinary numbers with:

SUBSTRING_INDEX(column, '.', 1) + IF(LOCATE(column, '.'), SUBSTRING_INDEX(column, '.', -1)/6, 0)

You can then use this expression in SUM() to add all the column in the table, or you can use it with + to add different columns to each other. E.g.

SELECT SUM(SUBSTRING_INDEX(column, '.', 1) + IF(LOCATE(column, '.'), SUBSTRING_INDEX(column, '.', -1)/6, 0)) AS total
FROM yourTable

If you also need to convert the sum back to your base 6 fraction representation, you can use:

CONCAT(FLOOR(total), '.', ROUND(MOD(total, 1) * 6)

So a full query might look like:

SELECT CONCAT(FLOOR(total), '.', ROUND(MOD(total, 1) * 6) AS total
FROM (SELECT SUM(SUBSTRING_INDEX(column, '.', 1) + 
                 IF(LOCATE(column, '.'), SUBSTRING_INDEX(column, '.', -1)/6, 0)) AS total
      FROM yourTable) as subquery