CODEWITHSUNDEEP
mysql
Albert Zhong
Albert Zhong

Reputation: 346

MySQL precision in AVG function with filtering

MySQL precision in AVG function with filtering

How MySQL handle precision when filtering on aggregated fields?

For example, I have the following table definition

CREATE TABLE `test_values` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `group` varchar(255) DEFAULT NULL,
  `value` decimal(20,4) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

With the following data:

id  group   value
1   A       1
2   A       2
3   A       3
4   A       4
5   A       0
6   A       0

Problem:

SELECT `group`, AVG(`value`) FROM test_values

yields

group   AVG(`value`)
A       1.66666667

no problem so far.

But when I add AVG(value) >= 1.66666667 to HAVING clause will return nothing:

SELECT `group`, AVG(`value`) FROM test_values
HAVING AVG(`value`) >= 1.666667

group   AVG(`value`)

It was weird that MySQL told me the result was 1.666667 but the record was not returned when 1.666667 applied as a filter.

What is the problem?

Upvotes: 1

Views: 95

Answers (1)

UkFLSUI
UkFLSUI

Reputation: 5672

The problem is stated by juergen d clearly in his comments, the value you have seen in the output i.e. 1.66666667 is not actually the value handled by mysql internally. It is actually 1.66666666........

So, one way is to round the AVG value at first and then compare. Try this:

SELECT `group`, ROUND(AVG(value), 2) AS AVG FROM test_values HAVING ROUND(AVG(value), 2) >= 1.67

Alternatively, you may want to use FORMAT in stead of ROUND in this particular case as well:

SELECT `group`, FORMAT(AVG(value), 2) AS AVG FROM test_values HAVING FORMAT(AVG(value), 2) >= 1.67

Upvotes: 1

Related Questions