Mysql float type issue

Question

I have posted a question here earlier and got an awesome answer! Stuck in building mysql query

But it lacks a one moment!

I am having a column in mysql table, defined as:

`bet_price` float(10,2) NOT NULL DEFAULT 0.00,

For a certain row the value is: 10000

A query like: SELECT bet_price, MIN(bet_price) AS min_price WHERE ID = :id

Will return a data like this:

bet_price | min_price
---------------------
 10000    | 10000.00

And in queries this part fails for me.

I've tried to use a functions like FORMAT and TRUNCATE - but this did not help me.

Is the linked question related to this problem? Is the default number format of your SQL client an actual issue or you're just curious about it? (In any case, I think a price deserves a column type that can store the exact value. See [Numeric Types](https://dev.mysql.com/doc/refman/5.7/en/numeric-types.html) for an overview.) — Álvaro González, Mar 26 '18 at 12:45
"`SELECT bet_price, MIN(bet_price) AS min_price FROM [table] WHERE ID = :id`" is a invalid SQL query.. You can't use non aggregated column(s) and aggregated column(s) together without group by and expect that you wil get correct relating data.. if the server had enabled only_full-group_by in the sql_mode his query would return a error see demo http://www.sqlfiddle.com/#!9/14a150/2 — Raymond Nijland, Mar 26 '18 at 12:57

ScaisEdge · Accepted Answer · 2018-03-26T13:13:16.007

2

could be there are some implicit conversion of some conversion in output rendering so if you need always same value then cast properly

   cast(a.bet_price as decimal(10,2), MIN(b.bet_price)

or

  a.bet_price , cast(MIN(b.bet_price) as UNSIGNED)

edited Mar 26 '18 at 13:13

answered Mar 26 '18 at 12:40

ScaisEdge

Nevermind, The query I received was wrong:{ it returned wrong ID, I will repost my question. Later, but thank you for your answer. By the way. FORMAT did not work, because it formatted the number like 10,000.00, TRUNCATE - works good. – Sergej Mar 26 '18 at 12:43
ok ... ..anyway cast should work properly in mysql .. (at least when i have used it ) – ScaisEdge Mar 26 '18 at 12:44
even with this it's a invalid SQL query.. You can't use non aggregated column(s) and aggregated column(s) together without group by and expect that you wil get correct relating data.. if the server had enabled only_full-group_by in the sql_mode his query would return a error see demo http://www.sqlfiddle.com/#!9/14a150/4 – Raymond Nijland Mar 26 '18 at 13:03
@RaymondNijland the question is stricly related to a format problem in the previous question by tth OP .. and is just a sample .. the OP mean the t the result becaming a MIN and the flat value is formatted in different way .. you can take a look at the question link in the OP question – ScaisEdge Mar 26 '18 at 13:07
ok i didn't follow the link i've only read this question.. the comment was based on the "SELECT bet_price, MIN(bet_price) AS min_price WHERE ID = :id" topicstarters query. – Raymond Nijland Mar 26 '18 at 13:13
1

@RaymondNijland .. anyway your comment is correct .. so i have removed the SELECT part and i refer to different column name for avoid confusion – ScaisEdge Mar 26 '18 at 13:14

1 Answers1