CODEWITHSUNDEEP
phpmysqlsqlcodeigniterrounding
Kanishka Panamaldeniya
Kanishka Panamaldeniya

Reputation: 17586

MySQL ROUND and AVG not working as expected

hi i am tying to use mysql ROUND and AVG functions ,

public function get_average_rating_by_specialty($shop_id,$where_array=array())
    {

        $this->replica->select('PreferenceToLOBID,PreferenceID , AVG(ROUND(AvarageRating)) as AvarageRating');
        $this->replica->from('*******');
        $this->replica->where(array('ShopID'=>$shop_id,'IsDelete'=>0));

        if($where_array)
        {
            $this->replica->where($where_array);
        }

        $this->replica->group_by('PreferenceID,PreferenceToLOBID');     
        $result = $this->replica->get();


        return $result->result_array();
    }

here i m trying to get average values after round the AvarageRating value,

i have only one record that matches my conditions in that row the AvarageRating value is 4.5

but the query result is 

Array
(
    [0] => Array
        (
            [PreferenceToLOBID] => 29
            [PreferenceID] => 654
            [AvarageRating] => 4.0000
        )

)

when i remove ROUND this works correctly , but i want to round the AvarageRating values before get the average

in this case i expect 4.5 to be the result , why it's returning 4

please help , thanks in advance.

Upvotes: 1

Views: 1253

Answers (2)

Richard EB
Richard EB

Reputation: 1027

You're asking it to round 4.5 to 0 decimal places, and then average it.

See http://dev.mysql.com/doc/refman/5.1/en/mathematical-functions.html#function_round

WRT to whether the answer should be 4 or 5, pay special attention to this part of the docs:

"For approximate-value numbers, the result depends on the C library. On many systems, this means that ROUND() uses the "round to nearest even" rule: A value with any fractional part is rounded to the nearest even integer."

Upvotes: 1

jaczes
jaczes

Reputation: 1404

ROUND(AvarageRating,1)

then You can get desired 4,5

Upvotes: 1

Related Questions