What is the best way to use number format in a MySQL query?

Question

I need to set the "Total Price" value to be a two decimal point value like "56.35". Now it's showing fraction values like "56.3566666". I need it to be formatted by MySQL "SELECT" query.

Answer 1

select
    format(field, 2) as formatted
from
    table

Do note that Format() returns a string, and the result will be with two decimal places (in the above example) - i.e. 100 will be formatted as 100.00.

Documentation.

Answer 2

That works too, but if you need it for further calculations or what not AND you have MySQL > 5.0.8 you could also try:

select
    cast(field as decimal(14, 2)) as formatted
from
    table

It is a bit more flexible this way! I like flexible...