CODEWITHSUNDEEP
mysqlmariadbaverage
Innovit
Innovit

Reputation: 103

MySQL AVG function gives more decimal than expected

I have data stored in MySQL DB version: 10.1.37-MariaDB, innodb_version: 5.6.41-84.1. I run a query to get the average of a column using AVG. 3 rows are returned and the values to average are 18.06, 18.44 and 20.05. The question is why AVG returns 18.849999999999998? The column data type is double.

select avg(col_0) from table where col_1='xxx' and col_2='yyy';

Upvotes: 1

Views: 1960

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522050

Double is not an exact type, it being a more precise version of float. You should use an exact numeric column type, if you want exact precision. From the MySQL documentation:

The DECIMAL and NUMERIC types store exact numeric data values. These types are used when it is important to preserve exact precision, for example with monetary data. In MySQL, NUMERIC is implemented as DECIMAL, so the following remarks about DECIMAL apply equally to NUMERIC.

You could get around this on the presentation side by rounding, but use an exact numeric type for a more long term solution.

Upvotes: 1

Siva
Siva

Reputation: 1529

You can use the FORMAT

select FORMAT(avg(col_0),2) from table where col_1='xxx' and col_2='yyy';

Upvotes: 0

Ashu
Ashu

Reputation: 1320

You can use round():

select ROUND(avg(col_0),2) from table where col_1='xxx' and col_2='yyy';

It will give you 2 decimals.

You can also use CAST()

select CAST(avg(col_0),2) AS DECIMAL (10,2)) from table where col_1='xxx' and col_2='yyy';

Upvotes: 1

Related Questions