Odd Ways to Round Numbers

September 21, 2005 Hey, Ted

SQL’s ROUND function allows me to round to the nearest whole number. How can I round to the nearest half?

–Chad

The ROUND function accepts two arguments–a number to be rounded and the number of decimal positions to round to. If the second argument is zero, the first argument is rounded to a whole number. If the second argument is positive, the number is rounded right of the decimal point (i.e., to tenths, hundredths, thousandths, etc). If the second argument is negative, the first number is rounded left of the decimal point (to tens, hundreds, thousands, etc).

The following example illustrates the ROUND function. A number is rounded to hundredths (two decimal positions), tenths (one decimal position), a whole number, tens, and hundreds.

select number,
       round(number,2), round(number,1),
       round(number,0),
       round(number,-1), round(number,-2)
  from mydata

Number    Round 2   Round 1   Round 0   Round -1  Round -2
878.787-  878.790-  878.800-  879.000-  880.000-  900.000-
434.343-  434.340-  434.300-  434.000-  430.000-  400.000-
   .000      .000      .000      .000      .000      .000
212.121   212.120   212.100   212.000   210.000   200.000
656.565   656.570   656.600   657.000   660.000   700.000

Now, to the question at hand. If you want to round to a fraction, you’ll have to do a little easy math. There are only three steps.

1. Multiply the number by the reciprocal of the fraction.

2. Round to a whole number.

3. Divide by the reciprocal of the fraction.

The following example rounds a set of numbers to the nearest quarter and the nearest half.

select number,                                          
       dec(round(number * 2, 0)/2,11,1) as Nearest_Half,
       dec(round(number * 4, 0)/4,11,2) as Nearest_Qtr  
 from mydata 

Number  Nearest Half   Nearest Qtr
  .00        .0           .00
 1.00       1.0          1.00
 1.10       1.0          1.00
 1.12       1.0          1.00
 1.13       1.0          1.25
 1.20       1.0          1.25
 1.25       1.5          1.25
 1.30       1.5          1.25
 1.37       1.5          1.25
 1.38       1.5          1.50
 1.40       1.5          1.50
 1.45       1.5          1.50
 1.50       1.5          1.50
 1.60       1.5          1.50
 1.62       1.5          1.50
 1.63       1.5          1.75
 1.70       1.5          1.75
 1.75       2.0          1.75
 1.80       2.0          1.75
 1.82       2.0          1.75
 1.83       2.0          1.75
 1.90       2.0          2.00
 2.00       2.0          2.00

This technique works for fractions with numerators other than one. Here’s a query that rounds to the nearest three-quarters.

select number,                                                    
       dec(round(number * 1.3333, 0)/1.3333,11,2) as Nearest_3qtrs
 from mydata

Number    Nearest 3Qtrs
  .000              .00
  .300              .00
  .400              .75
  .500              .75
  .700              .75
  .900              .75
 1.000              .75
 1.100              .75
 1.200             1.50
 1.300             1.50
 1.400             1.50
 1.500             1.50
 1.600             1.50
 1.700             1.50
 1.800             1.50
 1.900             2.25
 2.000             2.25
 2.100             2.25
 2.200             2.25
 2.300             2.25
 2.400             2.25

I don’t know how useful rounding to such a fraction is, so I won’t charge you extra for that information.

You can also round to multiples of a number. The steps are like those for fractions, but the math is easier if you reverse the multiplication and division operations.

1. Divide the number to be rounded by the number to which you’re rounding.

2. Round to a whole number.

3. Multiply by the number to which you’re rounding.

The following query rounds to the nearest 25 and nearest 50.

 select number,                                           
       dec(round(number / 25, 0)*25,11,0) as Nearest_25, 
       dec(round(number / 50, 0)*50,11,0) as Nearest_50  
 from mydata

Number     Nearest 25       Nearest 50
   0                0                0
 100              100              100
 110              100              100
 112              100              100
 113              125              100
 120              125              100
 125              125              150
 130              125              150
 137              125              150
 138              150              150
 140              150              150
 145              150              150
 150              150              150
 160              150              150
 162              150              150
 163              175              150
 170              175              150
 175              175              200
 180              175              200
 182              175              200
 183              175              200
 190              200              200
 200              200              200

SQL has two other rounding-like functions–CEILING (or CEIL) and FLOOR. CEILING returns the smallest integer that is greater than or equal to the numeric argument. FLOOR returns the largest integer less than or equal to the numeric argument. One common use of FLOOR is to truncate digits right of the decimal point, but TRUNCATE (or TRUNC) is better for that task because it works for negative numbers. TRUNCATE accepts the same arguments that ROUND accepts.

select number, round(number,0),
       floor(number), ceiling(number),
       dec(trunc(number,0),3,0)
  from mydata

Number      Round    Floor    Ceil    Trunc
878.787-      879-     879-    878-     878-
555.000-      555-     555-    555-     555-
434.343-      434-     435-    434-     434-
   .000         0        0       0        0
212.121       212      212     213      212
444.000       444      444     444      444
656.565       657      656     657      656

–Ted