creating a database on MySQL, how can I make a query involving odds

Question

i am creating a database on MySQL which involves gambling odds (i.e 2/1 or 11/2) MySQL doesn't recognise data in this format, so how can I create a data type for these gambling odds. I can of-course put these in decimal form, however I would like to know if it is possible to store them this way?

score 1 · Answer 1 · answered May 08 '12 at 22:33

1

you can store them plain-text in a VARCHAR column if you have no need to use them in calculations or ORDERing. Or you could use two columns, one for the first part, another for the second part of the fractions.

answered May 08 '12 at 22:33

Patrick Moore

13,251
5
38
63

thank you for your answer. Unfortunately I do need these for calculations. I thought about using two columns, but that would mean reformatting a frightful amount of data....just wondering if there is a more efficient solution out there... – LegItJoe May 08 '12 at 22:36
@LegItJoe: in what way would reformatting the data be onerous? If you have these values as strings at present, it can be exploded on the slash and inserted as two columns very easily. – halfer May 08 '12 at 22:51

score 1 · Answer 2 · answered May 08 '12 at 22:34

You could store the odds as a numerator/denominator pair using two fields. If you want to only use one field, the only sensible way would be using decimal form.

I recommend using the decimal value, unless you need to display the odds in a string, in which case it would probably be fine to use a numerator/denominator pair.

score 1 · Answer 3 · answered May 08 '12 at 22:44

I think storing them as decimal is the way forward, I would assume at some point you may want to do a calculation involving the odds, in which case decimal will be more convenient. You could use a lookup table to store the varchar representation as there are some quirky fractions out there, so it is not a case of finding the lowest proper fraction (e.g. 100/30, 6/4), so you could have a table like so:

Decimal    |    Fraction
1.5        |    2 / 3
2.0        |    Evens
3.0        |    6 / 4
3.33       |    100 / 30
4.0        |    3 / 1

creating a database on MySQL, how can I make a query involving odds

3 Answers3

Linked