SQL Round Function

Question

round(45.923,-1) gives a result of 50. Why is this? How it is calculated?

(sorry guys i was mistaken with earlier version of this question suggesting value was 46)

Answer 1

It doesn't for me on MySQL:

mysql> select round(45.923,-1);
+------------------+
| round(45.923,-1) |
+------------------+
|               50 |
+------------------+
1 row in set (0.00 sec)

Answer 2

And on Sql Server 2005:

select round(45.923,-1)
------
50.000

What database are you running this on?

Answer 3

ROUND(748.58, -1) 750.00

the second parameter: Lenght, is the precision to which numeric_expression is to be rounded. length must be an expression of type tinyint, smallint, or int. When length is a positive number, numeric_expression is rounded to the number of decimal positions specified by length. When length is a negative number, numeric_expression is rounded on the left side of the decimal point, as specified by length.

From

Answer 4

The SQL ROUND() function rounds a number to a precision...

For example:

round(45.65, 1) gives result = 45.7

round(45.65, -1) gives result = 50

because the precision in this case is calculated from the decimal point. If positive then it'll consider the right side number and round it upwards if it's >= 5, and if <=4 then round is downwards... and similarly if it's negative then the precision is calculated for the left hand side of decimal point... if it's >= 5

for example round(44.65, -1) gives 40 but round(45.65, -1) gives 50...

Answer 5

It is expected to be 50.

round(45.923, 0) => 46

expl: the last non-decimal digit is rounded (5), the desicion is based on the next digit (9) 9 is in the high half, ergo 5 is rounded up to 6

round(45.923, 1) => 45.9

expl: the first decimal digit is rounded (9), the desicion is based on the next digit (2) 2 is in the low half, ergo 9 stays 9

your case: round(45.923, 1-) => 45.92

expl: the secon-last non-decimal digit is rounded (4), the desicion is based on the next digit (5) 5 is in the top half, ergo 4 is rounded up to 5, the rest of the digist are filled with 0s

Answer 6

As for how, start by considering how you'd round a (positive) float to the nearest integer. Casting a float to an int truncates it. Adding 0.5 to a (positive) float will increment the integer portion precisely when we want to round up (when the decimal portion >= 0.5). This gives the following:

double round(double x) {
    return (long long)(x + 0.5);
}

To add support for the precision parameter, note that (for e.g. round(123456.789, -3)) adding 500 and truncating in the thousands place is essentially the same as adding 0.5 and to rounding to the nearest integer, it's just that the decimal point is in a different position. To move the radix point around, we need left and right shift operations, which are equivalent to multiplying by the base raised to the shift amount. That is, 0x1234 >> 3 is the same as 0x1234 / 2**3 and 0x1234 * 2**-3 in base 2. In base 10:

123456.789 >> 3 == 123456.789 / 10**3 == 123456.789 * 10**-3 == 123.456789

For round(123456.789, -3), this means we can do the above multiplication to move the decimal point, add 0.5, truncate, then perform the opposite multiplication to move the decimal point back.

double round(double x, double p) {
    return ((long long)((x * pow10(p))) + 0.5) * pow10(-p);
}

Rounding by adding 0.5 and truncating works fine for non-negative numbers, but it rounds the wrong way for negative numbers. There are a few solutions. If you have an efficient sign() function (which returns -1, 0 or 1, depending on whether a number is <0, ==0 or >0, respectively), you can:

double round(double x, double p) {
    return ((long long)((x * pow10(p))) + sign(x) * 0.5) * pow10(-p);
}

If not, there's:

double round(double x, double p) {
    if (x<0) 
      return - round(-x, p);
    return ((long long)((x * pow10(p))) + 0.5) * pow10(-p);
}