-1

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?

Community
  • 1
  • 1
user5697101
  • 571
  • 4
  • 12
  • 2
    *"is there any easy way to do it?"* - Define "easy". There are many ways of doing this. Ever heard of `count()`, or `sum()` or the `+` ? – Funk Forty Niner Dec 21 '15 at 20:12
  • @Fred-ii- How would `+` do this? He doesn't want to use ordinary arithmetic rules. – Barmar Dec 21 '15 at 20:14
  • @Barmar it's an indirect example. Sum = col1 + col2 + col3. question's unclear/too broad. – Funk Forty Niner Dec 21 '15 at 20:14
  • It seems like his real question is how to convert `3.5` to `3.833333` before adding it. – Barmar Dec 21 '15 at 20:15
  • *"If I want to get the sum of the values in these rows"* - *"value in a varchar column"* makes it even harder to calculate here, no? I honestly don't know why integers are stored in varchar to start with. – Funk Forty Niner Dec 21 '15 at 20:16
  • @Fred-ii- They're not integers. They're fractions, but the part after the `.` is sixths instead of tenths. – Barmar Dec 21 '15 at 20:20
  • @Barmar fair enough. so... what say we clear our comments to make room for potential other comments? if I see yours deleted, I'll delete mine. – Funk Forty Niner Dec 21 '15 at 20:20
  • Is `5.6` really a valid value in the column? Shouldn't that be `6.0`? – Barmar Dec 21 '15 at 20:21
  • 1
    First why would you store a numeric value in a varchar column in the first place? second, what does the table structure look like? third what attempts have you made? fourth you will have to convert it to base 10 before using a `SUM()` aggregate function, but I would recommend you change the table design to make this a simpler feat. AKA your table is not really normalized, you should add a column that has the fraction offset and store the number by itself. 3 goes in one column and 5/6 goes in another column. – John Ruddell Dec 21 '15 at 20:26
  • @Barmer no that's a typo sorry, should be 6.0 – user5697101 Dec 22 '15 at 10:19
  • @John Ruddell that seems the most efficient way of doing it thanks – user5697101 Dec 22 '15 at 10:19
  • @Fred-ii- they shouldn't be stored in a varchar, but they're floats not ints, and I didn't want to save it as a decimal in a float column because it's base-6. I define 'easy' as brevity and clarity of code, without compromising on effectiveness. – user5697101 Dec 22 '15 at 10:41
  • @MichealOMaolain It is a much better way to do it. Although Barmars answer does solve your problem, I wouldn't ever put that code into production ever. You should really consider updating your table to add a column that stores the base 6 division.. or even two columns with the numerator and the denominator.. that way you can calculate the division yourself when you want to and not when you don't want to. Just my two cents on the matter though. I'll throw you a +1 since it is a different question that others may need help with in the future :) – John Ruddell Dec 22 '15 at 18:26

2 Answers2

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
Barmar
  • 741,623
  • 53
  • 500
  • 612
0

You can add a new Virtual column to the Table where MySQL stores the normalized Value every time you change it. So you can sum this Column.

Change only the Tablename and the name for the Column 'col'

ALTER TABLE mytable
  ADD COLUMN norm float AS (FLOOR(col) + MOD(col, 1) / 0.6 ) PERSISTENT;

You can also put a index on the new field if you want.

Please let me know if it works for you

Bernd Buffen
  • 14,525
  • 2
  • 24
  • 39
  • I get an error when I try to execute this query `ALTER TABLE own_bowling ADD COLUMN overs_norm float AS (FLOOR(overs) + MOD(overs, 1) / 0.6 ) PERSISTENT;` _#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS (FLOOR(overs) + MOD(overs, 1) / 0.6 ) PERSISTENT' at line 2_ – user5697101 Dec 22 '15 at 10:45
  • sorry, ist to old. if can do, switch to MariaDB 10.1.9 – Bernd Buffen Dec 22 '15 at 11:11