Problem
This tip presents examples that illustrate how to alter results from the SQL ROUND function with its function parameter so that database values are easier to understand by those who need to reference your database results. By keeping its focus exclusively on the function parameter in the ROUND function, the tip aims to highlight the special benefits of the function parameter for the SQL Server ROUND function.
Solution
Rounding is a process for modifying and eliminating digits from underlying database values depending on their use case. For example, monetary values in some accounting and foreign exchange applications may require monetary values specified to four or more places after the decimal point. On the other hand, reporting the price of an item to the nearest penny needs only two places after the decimal point.
SQL Server’s built-in ROUND function supports two forms of rounding, sometimes referred to as traditional rounding and truncation.
This tip assumes you understand the basics of traditional rounding. If you require a refresher on traditional rounding, you may find either of these references helpful:
- SQL Server Rounding Functions – Round, Ceiling and Floor
- SQL Rounding Functions with ROUND, CEILING, FLOOR, Bankers Rounding.
The truncation style of rounding simply assigns zeros after the last untruncated digit in the original value. Among the resources that include coverage of the truncation style of rounding are:
The truncation examples in this tip focus on how to implement it for decimal data type values. Some modifications may be required for truncating other data type values or numerical expressions.
SQL ROUND Syntax
This is the basic syntax for the ROUND function. In this article, we will focus on the “function” parameter to show the how this impacts the results. If a numeric value is used for “function” it will truncate the result. If the “function” value equals 0 or is omitted the value is rounded.
ROUND ( numeric_expression , length [ ,function ] )ROUND Function Examples
The following script segment includes three pairs of examples after assigning and displaying a decimal value (123.456) to a local variable (@value). Each pair of examples contrasts traditional rounding with the truncation syntax for rounding.
- The first pair of SELECT statements demonstrate how to traditionally round or truncate to two places after the decimal point.
- The first SELECT statement shows traditional rounding of 123.456 to 123.460.
- The trailing 6 causes traditional rounding to transform the places after the decimal point from 456 to 460.
- The syntax for the SELECT statement assigns a value of 0 to the third round function parameter. This assignment specifies traditional rounding. You can also specify traditional rounding by simply omitting the third parameter for the round function.
- The second SELECT statement shows truncating 123.456 to 123.450.
- For truncation in this example, the round function simply overwrites the final digit (6) with a 0.
- In this example, the third-round function parameter is mandatory, and the parameter must have a value other than 0.
- Some database analysts may also be interested in the fact that the third parameter can have a tinyint, smallint, or int data type because the data type implies the range of values for the parameter.
- The first SELECT statement shows traditional rounding of 123.456 to 123.460.
- The second pair of SELECT statements demonstrates how to traditionally round or truncate to one place after the decimal point.
- The first SELECT statement shows traditional rounding of 123.456 to 123.500.
- The second SELECT statement shows truncating 123.456 to 123.400.
- The third pair of SELECT statements demonstrates how to traditionally round or truncate to zero places after the decimal point.
- The first SELECT statement shows traditional rounding 123.456 to 123.000.
- The second SELECT statement shows truncating 123.456 to 123.000.
- In this pair, the results are the same for both traditional rounding and truncating.
-- a decimal value with precision 6 and scale 3
declare @value dec(6,3) = 123.456select @value [original value]
---- 1 - truncation and rounding to two places after the decimal
select round(@value, 2, 0) [original value rounded to two places after decimal point] -- 123.460
select round(@value, 2, 1) [original value truncated to two places after decimal point] -- 123.450
-- 2 - truncation and rounding to one place after the decimal
select round(@value, 1, 0) [original value rounded to one place after decimal point] -- 123.500
select round(@value, 1, 1) [original value truncated to one place after decimal point] -- 123.400
-- 3 - truncation and rounding to zero places after the decimal
select round(@value, 0, 0) [original value rounded to zero paces after decimal point] -- 123.000
select round(@value, 0, 1) [original value truncated to zero places after decimal point] -- 123.000
Summing the Results with Traditional Rounding versus Truncating Examples
Truncation always results in a transformed value that is closer to zero when the digit after the last retained digit is not already zero. In contrast, traditional rounding always results in a transformed value that is higher than the original value whenever the digit after the last retained digit is 5, 6, 7, 8, or 9. As a consequence, the sum of a set of traditionally rounded values should be larger than the sum of the set of values rounded via truncation.
The following script confirms this outcome for the three pairs of samples that are rounded via traditional rounding versus truncation as in the preceding section.
As you can see, the following script segment merely sums the result sets separately for the three select statements implementing traditional rounding versus truncation. Each sum is displayed separately.
-- a decimal value with precision 6 and scale 3
declare @value dec(6,3) = 123.456
select @value [original value]
-- sum of truncated values
select (select round(@value, 2, 1)) + (select round(@value, 1, 1)) + (select round(@value, 0, 1)) [sum of truncated values]
-- sum of traditionally rounded values
select (select round(@value, 2, 0)) + (select round(@value, 1, 0)) + (select round(@value, 0, 0)) [sum of rounded values]Here’s the output from the preceding script. As you can see, the sum of the truncated values is closer to zero than the sum of traditionally rounded values.
Whenever it is advantageous to round towards zero, rounding with the round function parameter is a good practice. For example, a new business trying to grow its customer base may advertise that it rounds its total invoice amounts in favor of its clients to say thank you for their business.
Next Steps
The results in this tip drill down on rounding decimal data values based on the function parameter in the round function. However, there are other argument types that can be rounded with the round function, such as float and integer data type values as well as calculated values derived from multiplications or divisions. These alternative argument types do not necessarily have rounding and truncation implemented in the same way.
The topic of rounding data values can be very involved. For example, the DIYCalculator site describes and illustrates 16 different types of rounding, which are 14 more than the two covered in this short tip. Furthermore, rounding errors of various kinds are typical for different types of rounding. Slate describes a series of tiny rounding errors with enormous consequences in areas that are as diverse as politics, stock markets, and warfare. The history of rounding errors suggests that database analysts should be much more attentive to the type of rounding they use or even whether they should round at all! As a consequence, you should round wisely. This aims to help you do that.
If there is sufficient interest in this tip, follow-up tips may drill down on some of these related issues (so leave a comment if you want more coverage of truncation style rounding).
