Wednesday, 28 March 2007

MySQL - Double values return too many decimal places!

Ok, you may have a double value stored in a MySQL table of say "94.2348723". The problem is, this can look silly when displaying the data in a table and causes columns to be far too wide. Imagine the "94.2348723" as a price, so we would like to return "94.23" by editing the SQL statement, here's how:

Let's say the value is stored in a column "Price" like so:

ItemNo | Price
1                94.2348723
2                83.3821974

To round the figures correctly in MySQL, use the following:

SELECT `ItemNo`, Truncate(SUM(`Price`) + 0.0051,2) AS Price

It should now be correctly rounded to 2 decimal places!

No comments: