SQL Round Function

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

(sorry guys, I made a mistake with an earlier version of this question suggesting a value of 46)

+4
source share
6 answers

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

For instance:

round (45.65, 1) gives the result = 45.7

round (45.65, -1) gives the result = 50

because the accuracy in this case is calculated from the decimal point. If positive, then he will consider the right side number and round it up, if it is> = 5, and if <= 4, then round down ... and similarly, if it is negative, then the decimal point is calculated for the left side ... if it> = 5

e.g. round (44.65, -1) gives 40 but a round (45.65, -1) gives 50 ...

+11
source

ROUND (748.58, -1) 750.00

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

From

+4
source

It is expected to be 50.

round (45.923, 0) => 46

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

round (45.923, 1) => 45.9

expl: the first decimal digit is rounded (9), the assignment is based on the next digit (2) 2 is in the lower half, ergo 9 remains 9

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

expl: the last second decimal digit is rounded (4), the assignment is based on the next digit (5) 5 is in the upper half, ergo 4 is rounded to 5, the rest of digic is filled in 0s

+2
source

As for how, start by looking at how you should round the (positive) float to the nearest integer. Casting a float to an int truncates it. Adding 0.5 to the (positive) float will increment the integer part exactly when we want to round (when the decimal part is> = 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 (e.g. round(123456.789, -3) ) adding 500 and truncating in thousands of places is essentially the same as adding 0.5 and rounding to the nearest integer, it's just that decimal point in a different position. To move the radius point around, we need left and right shift operations that are equivalent to multiplying by the base raised to the shift amount. That is, 0x1234 >> 3 coincides with 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 that we can do the above multiplication to move the decimal point, add 0.5, truncate, and then do 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 path for negative numbers. There are several solutions. If you have an effective sign() function (which returns -1, 0, or 1, depending on whether the 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 are:

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

This is not for me in MySQL:

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

And on Sql Server 2005:

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

What database are you working in?

0
source

All Articles