Rounding mysql 0.5 doesn't always go up

Question

https://i.stack.imgur.com/pxEQW.png

CREATE TABLE `zz` (
  `jum_r` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `zz` VALUES (71045),(31875),(12045),(172125),(27325),(5465);


SELECT 
  jum_r, 
  ROUND(ROUND((jum_r * 1.1), 2), 2) as q_gross,
    ROUND(jum_r * 1.1) as gross,
    ROUND((jum_r * 10 / 100), 2) as q_ppn,
    ROUND(jum_r * 10 / 100) as ppn
FROM zz;

I have data according to the picture. Why does rounding 0.5 not always go up ...? What's wrong with my query? Thanks

please include a [MCVE](https://stackoverflow.com/help/mcve) in text form. — danblack, Feb 10 '20 at 02:49
[Why should I provide an MRE/MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) — Mukyuu, Feb 10 '20 at 03:04

score 3 · Accepted Answer · answered Feb 10 '20 at 03:07

3

For exact-precision numbers (e.g. DECIMAL) MySQL rounds 0.5 up to the next highest integer. For imprecise numbers (e.g. FLOAT) MySQL counts on the underlying C library's rounding, which is often "round-to-even". Doc ref here

answered Feb 10 '20 at 03:07

Bob Jarvis - Слава Україні

48,992
9
77
110

Mech · Answer 2 · 2020-02-10T03:33:11.913

0

After clarifying below, this should be your answer: CASE would help. Basically:

WHEN (ROUND(jum_r * 1.1) < 0.5) THEN FLOOR(ROUND(jum_r * 1.1)), WHEN (ROUND(jum_r * 1.1) >= 0.5 THEN CEILING(ROUND(jum_r * 1.1)). Not pretty but should work

edited Feb 10 '20 at 03:33

answered Feb 10 '20 at 02:55

Mech

3,952
2
14
25

I need 0.5 or more rounded up, 0.4 rounded down. CEIL always to up – Sandra Murdianto Feb 10 '20 at 03:06
Ah, I see. Perhaps using CASE would help. WHEN (ROUND(jum_r * 1.1) < 0.5) THEN FLOOR(ROUND(jum_r * 1.1)), WHEN (ROUND(jum_r * 1.1) >= 0.5 THEN CEILING(ROUND(jum_r * 1.1)). Not pretty but should work. – Mech Feb 10 '20 at 03:24
The question when dealing with a floating point number is - is the 0.5 that has been **printed** *really* 0.5, or is it 0.49999999999999999999924 or something like that? Just because a calculation *should* produce exactly 0.5 doesn't mean that it does when you're dealing with floating-point. See [What Every Computer Scientist Should Know About Floating Point Arithmetic](https://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html) and [Is floating point math broken?](https://stackoverflow.com/questions/588004/is-floating-point-math-broken). – Bob Jarvis - Слава Україні Feb 10 '20 at 13:40

Rounding mysql 0.5 doesn't always go up

2 Answers2