MySQL Multiplication seems slightly weird

[eluser]bapobap[/eluser]
Hi there,

I am getting data from MySQL, an average rating, which I'd like to present as a percentage. So, my query calculates the average of a number of reviews and gives me a number, say 4.5, which means 4.5 out of 5.

In order to get a percentage, I use this:

Code:

->select('rating * 20 AS percentage')

to get the value expressed as a percentage.

For some unknown reason however, MySQL will sometimes return 80 and sometimes 100 with a value of 4.5. I'm not using any other code or using any rounding, the query is just as it is above, so I have no idea why MySQL thinks 4.5 * 20 is equal to either 80 or 100.

Anyone have any ideas?!

[eluser]Michael Wales[/eluser]
I assume rating is a FLOAT, so force MySQL to do FLOAT * FLOAT rather than FLOAT * INT:

Code:

select('rating * 20.0 AS percentage');

[eluser]bapobap[/eluser]
Thanks Michael but it didn't work

Here is my query:

Code:

SELECT business.name, IFNULL(ROUND(AVG(reviews.rating), 1), 0) AS rating, rating * 20.0 AS percentage FROM (business) LEFT JOIN reviews ON reviews.business_id = business.id LIMIT 3

I've simplified this query a bit as it does some more work but related to other things.

And the result:

Code:

Array
(
    [0] => stdClass Object
        (
            [name] => Business 1
            [rating] => 4.5
            [percentage] => 80.0
        )

    [1] => stdClass Object
        (
            [name] => Business 2
            [rating] => 4.5
            [percentage] => 100.0
        )

    [2] => stdClass Object
        (
            [name] => Business 3
            [rating] => 4.0
            [percentage] => 80.0
        )

)

[eluser]Michael Wales[/eluser]
Damn, I have no idea - unfortunately. Are the variances occurring on the same system - have you tried another system (processes on FLOATS depend on the C libraries found on that system and can vary, since FLOAT isn't a real number - just an approximation of a number).

It's odd that it's giving two different results for rating * 20.0 and even the ones it provides are incorrect (should be 90).

What version of MySQL?

What if you change the rating field to DECIMAL type? You may be able to use MySQL's CAST function to typecast the return from ROUND() as a DECIMAL, just to ensure FLOAT approximation isn't messing with you.

Very tricky indeed...

[eluser]bapobap[/eluser]
I'm using MySQL 5.0.45 on my dev machine, not sure what's on the server, though I haven't tested it on the server.

I tried casting the value but it then gives me 5 *20 = 80, so not much better. I think I'll just get PHP to work it out for me.

Thanks for the help though!

[eluser]bapobap[/eluser]
Think I might have figured it out, the column name that stores the rating of a single review (loads of which make up the average) is called rating. It seems MySQL was multiplying one of these by 20, instead of my new average value that I was calling rating.

rating * 20.0 took the rating field of a review and multiplied it by 20 rather than my newly created average. This was even though I was grouping the reviews together and wasn't even including that column anywhere for the result.

So I called it something random, AS total_rating, but ->select('total_rating * 20.0 AS percentage') tells me there is no column called total_rating.

I'm not sure why that's happening, any ideas on that one?!

Thanks again

[eluser]daBayrus[/eluser]
i dont think you can do that, having a computation and giving it an alias and using the resulting alias as a column in the SELECT portion. The alias would be recognized I guess in the ORDER BY or HAVING clauses..

:wolv:

[eluser]therealmaloy[/eluser]
i guess i have to agree with daBayrus for this

tried before similar to this and it didn't work, so the best solution is to re-use the formula, i guess it's not a big performance loss.