MySQL Round Up to Nearest 5 Cents

nfs0ujit 于 2022-12-22 发布在 Mysql

关注(0)|答案(3)|浏览(154)

I need to round up values using MySQL to increments of 5 cents (0.05). It has to ALWAYS round up. Examples:
0.01 -> 0.05
2.12 -> 2.15
0.16 -> 0.20
How can I accomplish this. I tried a few things with ceil() and round(), but it seems like I can use some help from a MySQL expert.

mysql

来源：https://stackoverflow.com/questions/13081897/mysql-round-up-to-nearest-5-cents

3条答案

按热度按时间

b4lqfgs41#

Since 100/5 = 20 all we do is multiply by 20, round up to the nearest whole number, and then divide by 20.
(To round up in MySQL use CEIL)
Example for $1.03:

1.03 * 20   = 20.60
CEIL(20.60) = 21.0000
21 / 20     = 1.0500

NOTE: I also round the whole statement to 2 decimals places just to remove any trailing zeros.

SELECT ROUND( CEIL( (old_price) * 20) / 20, 2) FROM table

EDIT: A generalized example (notice the 100.00 to cast the value as non-integer)

SELECT ROUND( CEIL ( (old_price) * (100.00/the_increment)) / (100.00/the_increment), 2) FROM table

赞(0）回复(0）举报 2022-12-22

bqjvbblv2#

SELECT floor((<value> + 0.025) * 20) / 20

赞(0）回复(0）举报 2022-12-22

qnyhuwrf3#

Try to use this one -

SELECT (<value> DIV 0.05) * 0.05 + IF(<value> MOD 0.05 = 0, 0, 0.05)

赞(0）回复(0）举报 2022-12-22

我来回答

MySQL Round Up to Nearest 5 Cents

3条答案

相关问题

热门标签

最新问答