Mysql Round(x,d) where decimals in table

Question

For the example I have a the following table

 x double
 d tinyint

for different values I want to present different precisions, with for example

select round(x,d);

Does anybody have a good solution for this? stored procedure or something that works with the precision is saved in the same table as the values.

Please note that if D in round(X, D) is a column you will not get the expected results.

If you try the following:

USE test;
DROP TABLE IF EXISTS test_tbl;
CREATE TABLE test_tbl (x double, d tinyint) ENGINE MyISAM;
INSERT IGNORE INTO test_tbl (x,d) VALUES (1.12,3),(1.1234,3);
SELECT x,d,round(x,3),round(x,d) FROM test_tbl;

The result will be:

x      d  round(x,3)  round(x,d)
1.12   3  1.120       1.12
1.1234 3  1.123       1.123

Note the first round(x,d) is not 1.120, it will show as 1.12!

UPDATED, WORK-AROUND-SOLUTION

if you define a function as stated below you will get the expected result.

DELIMITER $$
DROP FUNCTION IF EXISTS StrRound;
CREATE FUNCTION StrRound(x double, d tinyint) RETURNS varchar(16) DETERMINISTIC COMMENT 'Returns string where x is rounded to d decimals (d from -6 to 6)'
BEGIN
  DECLARE s varchar(16);
  IF d=0 THEN SET s=cast(round(x, 0) as char);
  ELSEIF d=1 THEN SET s=cast(round(x, 1) as char);
  ELSEIF d=2 THEN SET s=cast(round(x, 2) as char);
  ELSEIF d=3 THEN SET s=cast(round(x, 3) as char);
  ELSEIF d=4 THEN SET s=cast(round(x, 4) as char);
  ELSEIF d=5 THEN SET s=cast(round(x, 5) as char);
  ELSEIF d=6 THEN SET s=cast(round(x, 6) as char);
  ELSEIF d=-1 THEN SET s=cast(round(x, -1) as char);
  ELSEIF d=-2 THEN SET s=cast(round(x, -2) as char);
  ELSEIF d=-3 THEN SET s=cast(round(x, -3) as char);
  ELSEIF d=-4 THEN SET s=cast(round(x, -4) as char);
  ELSEIF d=-5 THEN SET s=cast(round(x, -5) as char);
  ELSEIF d=-6 THEN SET s=cast(round(x, -6) as char);
  ELSE SET s=cast(x as char);
  END IF;
  RETURN s;  
END;$$
DELIMITER ;

using a decimal does not affect the outcome of the round function? — jorese, Feb 26 '13 at 15:48
You mentioned " that works with the precision" - and now working with precision in my point of view is working with decimals. There are a lot of variables that you have not mentioned - who will use this table?, logic and formula - handle in mysql? is just a display issue? etc. Reference for mysql double vs decimal - http://stackoverflow.com/questions/6831217/double-vs-decimal-in-mysql and http://stackoverflow.com/questions/4834390/how-to-use-mysql-decimal. Hope this helps — Anda Iancu, Feb 26 '13 at 16:05
Confusing enough I used precision instead of decimals which is more to the point. The problem is to save the number of decimals in mysql "on a per row basis" (same table as the value itself preferable). All normal solutions needs a predefined number of decimals per column, that is the case for the decimal column type or when you round of a field with "round(column, 3)" The question here is if somebody has a work-around solution for decimals per row. — jorese, Mar 27 '13 at 15:04

score 0 · Answer 1 · answered Apr 17 '13 at 17:52

According to MySQL manual: "The return type is the same type as that of the first argument" Also behaviour of ROUND depends on MySQL version

When I run your examples on MySQL 5.5.30 all trailing zeros were removed:

x      d  round(x,3)  round(x,d)
1.12   3  1.12        1.12
1.1234 3  1.123       1.123

So what you got there might be result rendering problem. Not sure why the behaviour differs as both types should be float (round(x,3) and round(x,d)).

anyhow you can force number of trailing zeros using FORMAT function:

SELECT x,d,FORMAT(round(x,3),3),FORMAT(round(x,d),d) FROM test_tbl;

http://sqlfiddle.com/#!2/5b125/3

Mysql Round(x,d) where decimals in table

UPDATED, WORK-AROUND-SOLUTION

1 Answers1