caramba caramba - 2 months ago 14
MySQL Question

SQL money round to closest 0.05 cents

I'm trying to round money in MySQL SELECT to the closest 0.05 cents.

so numbers like:

140.70 should become 140.70
140.71 should become 140.70
140.72 should become 140.70
140.73 should become 140.75
140.74 should become 140.75
140.75 should become 140.75
140.76 should become 140.75
140.77 should become 140.75
140.78 should become 140.80
140.79 should become 140.80


So more in detail

0.00 = 0.00
0.01 = 0.00
0.02 = 0.00
0.022 = 0.00 // here the magic should happen 0.022 is closer to 0, so result is 0
0.023 = 0.05 // but 0.023 should be rounded to 0.05! cause first round 0.023 to 0.025 which should then be rounded up to 0.05
0.03 = 0.05


I've tried some different things with MySQL CEIL() and MySQL FLOOR() but couldn't get the right result.

Created a SQL Fiddle here

With a table which makes no sense, except we need one to SELECT from:

CREATE TABLE hello ( world varchar(255) );
INSERT INTO hello (world) VALUES ('blubb');


This is the select Query:

SELECT

CEILING ( 0.05 / 0.05 ) * 0.05 AS CEIL_1,
CEILING ( 0.06 / 0.05 ) * 0.05 AS CEIL_2,
CEILING ( 0.07 / 0.05 ) * 0.05 AS CEIL_3,
CEILING ( 0.08 / 0.05 ) * 0.05 AS CEIL_4,
CEILING ( 0.09 / 0.05 ) * 0.05 AS CEIL_5

FROM hello;


Anyone here telling me how to do it right?

Answer
SELECT ROUND(140.77/5,2) * 5;
+-----------------------+
| ROUND(140.77/5,2) * 5 |
+-----------------------+
|                140.75 |
+-----------------------+
Comments