Search code examples
sqlsql-servert-sqlfloor

How to floor a number in sql based on a range

I would like to know if there is a function or some sort of way to round a number to lowest whole value. Something like floor, but to a specified increment like 10. So for example:

0.766,5.0883, 9, 9.9999 would all be floored to 0 11.84848, 15.84763, 19.999 would all be floored to 10 etc...

I'm basically looking to fit numbers in the ranges of 0, 10, 20, 30, etc

Can I also do it with different ranges? For example 0, 100, 200, 300, etc

Thank you.

Solution

  • You can do this with arithmetic and floor():

    select 10*floor(val / 10)

    You can replace the 10s with whatever value you want.