I have a request

select (SELECT SUM(ostatok) FROM ostatky) AS ost

The result of the query is a floating point number. How to make the result displayed like this: if there is a fractional part, then it shows a number with two digits after the comma, and if there is no fractional part, then after the comma it puts two zeros. How to do it?

    3 answers 3

    MySQL has a rounding function ROUND(n, m) . n is a number, m is the number of decimal places.

     SELECT ROUND(10.555, 2)

    Displays 10.56 .

    UPD: There is also a FORMAT function that rounds the fractional part and formats the number in #,###,###.## . After processing with FORMAT, commas can be replaced with spaces and at the output a rounded number with digits separated by spaces

     SELECT REPLACE(FORMAT(12345.678, 2), ',', ' ') # вывод 12 345.68
    • but, if it is not 10.555, but 10, then the result will be just 10, but I need 10.00 - Leonid
    • Single-type queries return a set of columns of fixed types in spite of a variable condition. That is, there will be not 10 , but 10.0 . In this case ROUND will return 10.00 . - KiTE
    • And SELECT ROUND (10, 2) will display 10.00? - Leonid
    • can still tell me how to split the result of the query in three digits, as in the money format ... for example ... 45 456.12 - Leonid
    • No, SELECT ROUND(10, 2) will display just 10 . And, here, SELECT ROUND(10.0, 2) already displays 10.00 . That is, if the original expression is floating point, then the result is also floating point. Completed the answer with a variant using FORMAT() . Through it, you can solve the problem with discharges. But, in my answer, I answered the question "how." And, in general, I share the opinion of @renegator. Formatting output is an application level and not a database. - KiTE

    Data mapping is the work of the application that uses the data. It is necessary to pervert

       select (SELECT cast(SUM(ostatok) as dec(8,2)) FROM ostatky) AS ost
      • Why is nested select here? o_O - Sh4dow
      • Not needed, but I left the author's "spelling and punctuation", because the question was different, and I didn’t want to be tedious. :-) - msi
      • By the way, all the same, your option does not plow ( - Leonid
      • What exactly? If the size is not enough, so increase. - msi