How to round to the nearest whole multiple or fraction in SQL
Every once in a while, I find myself needing to round a number up to the nearest even power of 10, or round a time interval to the nearest quarter of an hour, or something like that. This is actually quite simple, but for some reason I always find myself confused about how to do it. I have to reason it out all over again, instead of just remembering how to do it. Perhaps writing this blog post will help me remember next time.
The basic idea for rounding to whole multiples is to divide the number, losing precision. Then round, floor, or ceiling the resulting number, and multiply to get back to the original magnitude. For rounding to fractions, reverse the process: multiply, round and divide again.
This actually works for any programming language, not just SQL. But I find myself doing it in SQL most often.
Here’s an example of how to turn a year into a decade:
mysql> SELECT FLOOR(YEAR(NOW()) / 10) * 10 AS decade;
+--------+
| decade |
+--------+
| 2000 |
+--------+There are other ways to do this, of course. In this case, since the original year is expressed in decimal notation, and we are rounding down to the nearest power of 10, we could simply take the leftmost three digits and add a zero. But that wouldn’t work if we were trying to “snap” to the nearest five-year interval. The technique I showed above does:
mysql> SELECT FLOOR(YEAR(NOW()) / 5) * 5 AS half_decade;
+-------------+
| half_decade |
+-------------+
| 2005 |
+-------------+Let’s suppose we want to take an arbitrary number, and round it to the nearest 1/8th. In this case, we need to divide by 1/8 and then multiply by 1/8 again to get to the nearest fraction, because dividing by eight and multiplying by eight would actually get us to the nearest even power of eight. I’ll just select random numbers between zero and 100 from one of the system tables to illustrate:
mysql> SELECT ROUND((RAND() * 100) / .125) * .125 AS nearest_eighth
> FROM mysql.help_topic LIMIT 10;
+----------------+
| nearest_eighth |
+----------------+
| 42.875 |
| 27.875 |
| 10.875 |
| 70.375 |
| 19.625 |
| 86.875 |
| 75.750 |
| 17.750 |
| 61.500 |
| 54.500 |
+----------------+Of course, 1/8 is an easy number to write out in decimal: .125. It would not be so easy to write out 1/14. So naturally, we can do this by using inverses.
mysql> SELECT ROUND((RAND() * 100) * 14) / 14 AS nearest_14th
> FROM mysql.help_topic LIMIT 10;
+--------------+
| nearest_14th |
+--------------+
| 88.0714 |
| 76.7857 |
| 19.6429 |
| 67.8571 |
| 80.2857 |
| 98.0714 |
| 49.2857 |
| 52.2143 |
| 13.3571 |
| 10.0000 |
+--------------+I hope this was useful to you. I’m betting I’ll be referring back to it myself the next time I need to round a number to the nearest fraction or whole multiple of some other number.
Is the tag incorrect? Posted in: Coding, PostgreSQL, SQL. Shouldn’t be MySQL, SQL?
M
10 Aug 09 at 4:42 am
It’s a long story, but once upon a time the SQL category got added to the MySQL planet aggregator and the PostgreSQL category got added to the Postgres planets, so I really use SQL as though it’s MySQL and post to both categories if it’s not specifically about MySQL.
Xaprb
10 Aug 09 at 8:18 am
The use of mysql.help_topic as a driving table is ingenious!
Shlomi Noach
10 Aug 09 at 1:22 pm
I believe you mean “multiple of” 8 (or 10), not “power of”
Matt
15 Aug 09 at 8:12 am
hi
if i want to round any whole number shown in an interval from 6003.0 to 6007.0 to always show as 6005.0 in that interval and again if the number shown is from 6008.0 to 6112.0 to always show as 6010 how would i multiple this and round to get my answer 6005 and 6010 respectively.
i am at sixes and sevens here .
nari
19 Aug 09 at 12:19 pm
sixes and sevens no more i just multiplied the number far out then round((),-5) and divide by same number so now i get the numbers from 6003 to 6007 shown as 6005 and 6008-6112 shown as 6010, 6113 to 6117 as 6015 and so on….
:)
n
19 Aug 09 at 7:39 pm
hi found problem if the number equals 6007.5000 or 6002.5000
will always round up ,i used
minnumber= CAST (cast ((round(min(number) *20000,-5)) as decimal )/20000 AS NUMERIC (8,4))
this sets the number intervals in 5.0 point increments and it always rounds up if number is at ‘%%%2.5%%%’ and ‘%%%7.5%%%’(ie, midpoint of the 5 point intervals)
how can i set these two numbers so they show as 6000.0000 and 6005.000 respectively whilst not changing the rest
any ideas
n
21 Aug 09 at 11:24 am
hi
no problem either
i ran
update table 1
set minnumber=minnumber -5 where min(number)like ‘%%%2.5%%%’
update table 1
set minnumber=minnumber -5 where min(number)like ‘%%%7.5%%%’
so whereas before any number ending like %%%2.5%%% was getting rounded up to 6005 when i wanted it as 6000 and any number ending up like ‘%%%7.5%%%’was getting rounded up to 6010 when i wanted it as 6005 now with the update function everything is corrected.
(isn’t this a nice way to get around the round up problem so simple yet logical)
i know it an extra step but it works and that should count even for a newbielike me (second week learning sql:))
beats spending the next few days trying to work out a formula im sure there is one but time dictates if it works move on.
n
21 Aug 09 at 9:37 pm
[...] other day, Baron Schwartz posted How to round to the nearest whole multiple or fraction in SQL. In an offhand way, he generated some random numbers using the mysql.help_topic table. I then [...]
Generating numbers out of seemingly thin air | code.openark.org
1 Sep 09 at 2:39 am
Ah, perfect, thanks! I have a line graph that displays millions of dollars. I wanted to set the Y axis to the farthest multiple of $50m over and under the current day’s value. This did the trick niceley, and now I don’t have to manually update and hard code the axis min & max every few months.
Beverley M
20 Jan 10 at 5:53 pm