Rounding is a complicated topic. It is essentially changing the value of a number to an approximate defined by different rules. You could always round up or down, round half up or half down, towards zero or away from zero, or even to the nearest even or odd number. This article won’t look to define each one of them. It’s just going to show some basics.
Available Functions
The common option to round numbers is the ROUND() function, which uses the following syntax:
ROUND ( numeric_expression , length [ ,function ] )
The numeric expression is the number that we’re rounding. The length is the position to the right of the decimal point (negative values will count places to the left of the decimal point). The last argument allows us to choose between rounding and trunctating.
The default behavior for SQL Server is to round half away from zero as shown on Table 1.
Original Value | Resultant value when rounding to integer |
3.2 | 3 |
3.7 | 4 |
3.5 | 4 |
-2.3 | -2 |
-2.8 | -3 |
-2.5 | -3 |
Two other functions are available: CEILING() and FLOOR(). These functions have no length as they’ll only round up or down, respectively, to the closest integer. Note that when dealing with negative numbers, CEILING will continue to go away from zero. E.g. CEILING(-2.3) will return -2.
Here’s an example for basic rounding (the same sample data will be used on all the examples of this article) :
CREATE TABLE #Decimals( OriginalValue decimal(10,4) ); INSERT INTO #Decimals VALUES (3.23), (3.76), (3.15), (3.5), (-2.34), (-2.89), (-2.25), (-2.5) , (-2.2) ; SELECT OriginalValue, ROUND(OriginalValue, 0) UsingRound, ROUND(OriginalValue, 0, 1) UsingRoundWithTruncate, FLOOR(OriginalValue) UsingFloor, CEILING(OriginalValue) UsingCeiling FROM #Decimals;
With the following results:
OriginalValue UsingRound UsingRoundWithTruncate UsingFloor UsingCeiling --------------- -------------- ------------------------- -------------- -------------- 3.230 3.000 3.000 3 4 3.760 4.000 3.000 3 4 3.150 3.000 3.000 3 4 3.500 4.000 3.000 3 4 -2.340 -2.000 -2.000 -3 -2 -2.890 -3.000 -2.000 -3 -2 -2.250 -2.000 -2.000 -3 -2 -2.500 -3.000 -2.000 -3 -2 -2.200 -2.000 -2.000 -3 -2
Simulating CEILING and FLOOR with different lengths
CEILING() and FLOOR() have the important restriction to return just integers, but sometimes we need different lengths. Here’s an example on how to simulate the functionality for these functions with different lengths. I’m using a variable for the Length to make it easier to identify what needs to be changed on each formula to achieve different lengths. I’m also using a variable for 10 to define it as float and avoid truncation when using POWER().
DECLARE @Length int = -1, @10 float = 10; SELECT OriginalValue, --1st Option FLOOR( OriginalValue*POWER(@10,@Length))/POWER(@10,@Length) SimulatingFloor, CEILING(OriginalValue*POWER(@10,@Length))/POWER(@10,@Length) SimulatingCeiling, --2nd Option ROUND(OriginalValue-(.49*POWER(@10,-@Length)),@Length) SimulatingFloor2, ROUND(OriginalValue+(.49*POWER(@10,-@Length)),@Length) SimulatingCeiling2 FROM #Decimals;
Here are the results for the previous query:
OriginalValue SimulatingFloor SimulatingCeiling SimulatingFloor2 SimulatingCeiling2 ----------------- ------------------- -------------------- ------------------- -------------------- 3.230 3.20000000000 3.30000000000 3.2 3.3 3.760 3.70000000000 3.80000000000 3.7 3.8 3.150 3.10000000000 3.20000000000 3.1 3.2 3.500 3.50000000000 3.50000000000 3.5 3.5 -2.340 -2.40000000000 -2.30000000000 -2.4 -2.3 -2.890 -2.90000000000 -2.80000000000 -2.9 -2.8 -2.250 -2.30000000000 -2.20000000000 -2.3 -2.2 -2.500 -2.50000000000 -2.50000000000 -2.5 -2.5 -2.200 -2.20000000000 -2.20000000000 -2.2 -2.2
Conditional rounding
In some cases, people need to round only if it would round towards zero. In that case, we just need to identify which numbers should be rounded and which shouldn’t. In other words, we need to identify which numbers would return the same output when rounded and truncated. Identifying that part, the rest just needed a CASE expression.
SELECT OriginalValue, CASE WHEN ROUND(OriginalValue, 0) = ROUND(OriginalValue, 0, 1) THEN ROUND(OriginalValue, 0) ELSE OriginalValue END FROM #Decimals;
The results come like this:
OriginalValue ConditionalRound ---------------- ------------------ 3.230 3.000 3.760 3.760 3.150 3.000 3.500 3.500 -2.340 -2.000 -2.890 -2.890 -2.250 -2.000 -2.500 -2.500 -2.200 -2.000
Get the decimal part of a number
As mentioned at the beginning of this article, rounding is changing the value of a number. Sometimes, we would need to find the difference between these numbers, either to get the decimal part or evaluate how large the sum of these differences would be. This is achieved by subtracting the rounded value from the original value.
SELECT OriginalValue, OriginalValue - ROUND(OriginalValue, 0) RoundingDifference, OriginalValue - ROUND(OriginalValue, 0, 1) DecimalPart FROM #Decimals;
Here’s the result from the previous query.
OriginalValue RoundingDifference DecimalPart ----------------- --------------------- ------------- 3.230 0.230 0.230 3.760 -0.240 0.760 3.150 0.150 0.150 3.500 -0.500 0.500 -2.340 -0.340 -0.340 -2.890 0.110 -0.890 -2.250 -0.250 -0.250 -2.500 0.500 -0.500 -2.200 -0.200 -0.200
Cautions when rounding and aggregating
As mentioned, rounding can cause problems when aggregating the data. It’s especially noticeable when adding the values. Notice that you need to define if the sum needs to be precise or if it needs to be congruent with the values shown. Depending on the situation, be sure to define if rounding should be done before or after the aggregation as it can return different values. Even with our small set of values, we’re getting a difference.
SELECT SUM(OriginalValue) OriginalValueSUM, ROUND(SUM(OriginalValue), 0) RoundAfterAggregation, SUM(ROUND(OriginalValue, 0)) RoundBeforeAggregation FROM #Decimals;
The results from this query are:
OriginalValueSUM RoundAfterAggregation RoundBeforeAggregation ------------------- ------------------------ ------------------------ 1.460 1.000 2.000
Conclusion
Lots of rules can be defined for rounding as there isn’t a definite definition on how it should be done. I hope that these examples can help you to solve problems you’ll face at work and be sure to share in the comments if you have solved a different problem.