I'm trying to round money in MySQL SELECT to the closest 0.05 cents.
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
in more 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, result is 0
0.023 = 0.05 //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?
SELECT ROUND(140.77/5,2) * 5;
+-----------------------+
| ROUND(140.77/5,2) * 5 |
+-----------------------+
| 140.75 |
+-----------------------+