# 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