Rounding numbers to the nearest 10 in Postgres
I have struggled with an equivalent issue. I needed to round number to the nearest multiple of 50. Gordon's suggestion here does not work.
My first attempt was SELECT round(120 / 50) * 50
, which gives 100
. However, SELECT round(130 / 50) * 50
gave 100
. This is wrong; the nearest multiple is 150
.
The trick is to divide using a float, e.g. SELECT round(130 / 50.0) * 50
is going to give 150
.
Turns out that doing x/y
, where x
and y
are integers, is equivalent to trunc(x/y)
. Where as float division correctly rounds to the nearest multiple.
If you want to round to the nearest 10, then use the built-in round()
function:
select round(<whatever>, -1)
The second argument can be negative, with -1 for tens, -2 for hundreds, and so on.
To round to the nearest multiple of any number (range):
round(<value> / <range>) * <range>
“Nearest” means values exactly half way between range boundaries are rounded up.
This works for arbitrary ranges, you could round to the nearest 13
or 0.05
too if you wanted to:
round(64 / 10) * 10 —- 60
round(65 / 10) * 10 —- 70
round(19.49 / 13) * 13 -- 13
round(19.5 / 13) * 13 -- 26
round(.49 / .05) * .05 -- 0.5
round(.47 / .05) * .05 -- 0.45