How to round only non integer numbers to one decimal place in MySQL


Problem :

I'm using MySQL and I want to display the values (which are all decimals) from a SELECT query as follows: when the number has non zero decimals, then it should be rounded to one decimal place; when it is an integer, then to display with no decimal places.

Hence 3.26 should be displayed as 3.3 whereas 5.00 should be displayed as 5.

When I use ROUND(x,1) to the above I get 3.3 and 5.0 respectively.



Solution :

Try like this

select  
case (yournumber mod 1 > 0)  
  when true then round(yournumber, 1)   
  else round(yournumber,0) 
end 
from tablename;

Mysql Tutorials

Mysql Howto..

How to generate a sequence number using JPA and MySQL?

How to connect to mysql database using java?

How to match array of mobile numbers with Mysql data?

How to implement Slick + MySQL + SecureSocial?

How can i pair images in my DB?

How to insert new values in MySql tables only those differents from rows that already exists?

How to implement a 'Favorite' relation in MySQL database?

How do I add data to different tables but keep the same id?

How to benchmark and optimize a really database-intensive Rails action?

How to union queries from the same table in MySQL

How to call a mysql function in phpmyadmin?

How to feed mysql queries from bash

During Mysql Excel upload using PHP how to validate mobile numbers column? [closed]

how can i define mysql's empty return?

MySQL - Relational tables - How to create multiple relationships?

How to get rows with array of ids in MySQL

PHP MYSQL: how to display chat room messages based on ignore list

How to change 0 value to '-' in mysql

How to share a one mysql connection between multiple threads?

MySQL - How to query items by tags, order by matched tag count

How to change data type when running query (not inside PHP)

how to show specific to mysql database

How to escape backslashes in mysql with JDBC? [duplicate]

How to improve MySql query that trying to find duplicate entries in large database?

How to avoid mysql 'Deadlock found when trying to get lock; try restarting transaction'

How to properly GROUP BY in MySQL?

How do I fix: “…error in your SQL syntax; check the manual for the right syntax”

How do I sorting MySQL result by ages from the birthday date

How to set the SUM (CASE WHEN) function's return to 0 if there are not values found in MYSQL?

How to select second record after first from mysql base