{"id":103541,"date":"2024-09-23T02:38:51","date_gmt":"2024-09-23T02:38:51","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=103541"},"modified":"2024-08-21T02:43:37","modified_gmt":"2024-08-21T02:43:37","slug":"rounding-numbers","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/theory-and-design\/rounding-numbers\/","title":{"rendered":"Rounding Numbers"},"content":{"rendered":"\n

I would hope by now everybody has figured out when you see a price like $39.99 that it might as well be forty bucks. Dropping that penny from the price is done is to fool the consumer! Since we read numbers left to right, the merchant hopes you will see the 3 and assume the price is emotionally less than 40. This is the concept of \u201csignificant digits\u201d which figures in the rounding rules.<\/p>\n\n\n\n

This concept of \u201clying to yourself\u201d can be formalized with different functions and can turn out to be useful in the real world. I am going to be somewhat informal in this discussion, so bear with me. Let\u2019s start off with the two functions that you see in programming languages and everyday arithmetic.<\/p>\n\n\n\n

FLOOR() and CEILING() Functions<\/h2>\n\n\n\n

The FLOOR(x)<\/code> function is easy to define informally. Cut off the fraction and simply return the integer part of a real number. This is what you were doing when you Ignored the pennies in those prices I just talked about. There is actually more to worry about than just this simple heuristic.<\/p>\n\n\n\n

When you took freshman calculus, those functions of one variable that you studied were nice and smooth. However, when you graph out FLOOR(x)<\/code>, You get a \u201cstaircase\u201d of half open intervals, with one \u201cstep\u201d for each integer. The domain Is all the real numbers, and the range is all the integers. But this function is not continuous; think about taking the first derivative and you have problems. Likewise, if you try to take the integral, there can be some problems with the half open intervals. Do not worry about this, unless you\u2019re in a calculus class.<\/p>\n\n\n\n

When the parameter is a positive real number or zero, such as FLOOR(21.53) = 21<\/code>, this is natural and easy. But when the parameter is negative, such as FLOOR(-21.53) = -22<\/code>, you must remember that negative numbers get smaller as you move away from zero. The CEILING()<\/code> function returns the smallest integer value which is greater than or equal to it\u2019s parameter. This means when the parameter is positive, CEILING(21.53); = 22 <\/code>and when the parameter is negative, CEILING(-21.53) = -21<\/code>.<\/p>\n\n\n\n

You can write the FLOOR(x)<\/code> or the CEILING(x)<\/code> with an expression in the parameter using the other function. The CEILING(x)<\/code> function returns the next highest integer. You probably won\u2019t see CEILING(x)<\/code> used too often because it\u2019s generally easier to write it in terms of FLOOR(x+1)<\/code>.<\/p>\n\n\n\n

TRUNC function<\/h3>\n\n\n\n

Truncation is another function related to FLOOR and CEILING and that is the truncation function TRUNC(x,d)<\/code>. The name pretty well explains itself. There\u2019s no attempt to change a digit on the cusp. This function will simply chop off the digits below a certain degree of precision (which is the second parameter). <\/p>\n\n\n\n

SELECT FLOOR(54.4) AS Floor, CEILING(54.4) AS Ceiling, \n       TRUNC(54.9,0) AS Trunc;<\/pre>\n\n\n\n
Floor   Ceiling     Trunc<\/code>\n------- ----------- -------------<\/code>\n54      55          54.0<\/code><\/pre>\n\n\n\n
And for the negative values: <\/p>\n\n\n\n
SELECT FLOOR(-54.4) AS Floor, CEILING(-54.4) AS Ceiling, \n       TRUNC(-54.9,0,1) AS Trunc;<\/pre>\n\n\n\n
Floor   Ceiling     Trunc<\/code>\n------- ----------- -------------<\/code>\n-55     -54         -54.0<\/code><\/pre>\n\n\n\n
Rounding<\/strong><\/h2>\n\n\n\n
All these functions just discussed are useful, but there are situations where reducing a real number to an integer is not useful. This is where we get into what people usually think of rounding to be. We talk about rounding to (n) decimal places. In fact, this is a parameter in the ROUND()<\/code> function in SQL Server and most other SQL products so you can decide if you want to round to the nearest penny, nearest dollar, or whatever unit your measurement is in.<\/p>\n\n\n\n
Decades ago, before we had calculators and had take square roots by hand, we were taught that when we wanted to round up a number to (n) decimal places, the rule was to look at the (n+1)<\/code> decimal place. If position(n+1) < 5<\/code>, then it was rounded down; if position(n+1) > 5<\/code> then increment that digit, rounding up. Thus 3.141 became 3.14 and 3.147 became 3.15. The problem was with 3.145 because we didn\u2019t have a clear rule for position(n+1) = 5<\/code> digit.<\/p>\n\n\n\n
Of course, typically you will round up the digit with the value of 5, but it is important to understand the rule because it can be different.<\/p>\n\n\n\n
Scientific Rounding<\/h3>\n\n\n\n
The rule I just gave was known as scientific rounding back in the dark ages of public school education. The usual correction given for the cusp case of five was if position(n+1) >= 5 <\/code>then increment. This was a \u201cbenefit of the doubt.\u201d sort of rule.<\/p>\n\n\n\n
It typically worked well in situations where the value being rounded was a value in a relatively small set, usually one data point. Since this is how a scientific experiment would behave, we call it scientific grounding.<\/p>\n\n\n\n
In SQL Server, we can use the FLOOR()<\/code> function to round down a decimal number to the nearest integer. For example, if you have a DECIMAL (2,1)<\/code> number (3.8), the FLOOR<\/code> function will round it down to 3. In regular arithmetic, this is just fine, but SQL is a strongly typed language; we really wanted (3.0) to match the DECIMAL<\/code> data type used in the DDL. The trailing zeros tell us to what number of decimal places the original calculations were done. In some situations, you might also consider displaying leading zeros to show the source data type.<\/p>\n\n\n\n
Commercial Rounding<\/h3>\n\n\n\n
In the old days, when we were given a long list of values and got a five, we wanted to round up or down In such a way as to average out the errors, so that the totals would be more accurate. It\u2019s called commercial rounding because it was first used in banks and financial computations.<\/p>\n\n\n\n
The rules were the same as the scientific rounding for digits not equal to five. When you got to five you were to go over to the position (n+2) digit and see if it was odd or even To determine whether you\u2019re going to round up or down. If this position has another five, then go to the position (n+3) digit and repeat the process until you got an answer or ran out of digits. The idea was that as you got to smaller and smaller decimal fractions, the digits would tend to be random. This randomness would smooth out total errors.<\/p>\n\n\n\n
In fact, there\u2019s a piece of IT folklore that crops up every now and then about an embezzler who saves the rounding errors until they accumulate enough to be worth cashing out. The version of this folklore that I learned was a story about a bank that had promotional campaign. They wanted to look at their customers \u201cfrom A to Z\u201d in an ad campaign, so they pulled off the customers with names at both ends of the alphabet. The first customer was named something like \u201dAaron\u201d and the last customer was \u201cZwabo\u201d; this last customer did not really exist. It was a dummy account set up by the embezzler where all the rounding errors were accumulated.<\/p>\n\n\n\n
Statistical Rounding<\/h3>\n\n\n\n
If you know something about the population, Assuming that the distribution of odd and even digits after position(n) would be evenly distributed is a leap of faith. If we know something about our population, then we can determine if the data is skewed. For example, if I am measuring liquids, I would expect the total number of liters pumped to customers to be less than the total number of liters In the original storage tank. Between evaporation, spillage and cumulative rounding error at the pump, I should see a slight loss.<\/p>\n\n\n\n
The systematic sort of error occurs in many engineering applications. To give you another piece of folklore, when Rolls-Royce decided to use automatic transmissions in their automobiles, they bought patents rights from an American motor company. Why reinvent the wheel?<\/p>\n\n\n\n
Unfortunately, they built these transmissions to Rolls-Royce tolerances and precision. The gears meshed so precisely that transmission fluid. was squeezed out. The transmissions did not work. Another example is that you can get a handgun in which the parts fit together so precisely that the gun has a kick that make it almost impossible to aim after the first shot. This is real mess for quality control people; they can check the individual parts and find that they are just what they\u2019re supposed to be, but the machinery as an assembled whole does not work.<\/p>\n\n\n\n
The ROUND() Function<\/h3>\n\n\n\n
As to have parameters for the value you want to round and how much rounding you want to do. The syntax for the ROUND<\/code> function in Oracle, MySQL and PostgreSQL is:<\/p>\n\n\n\n
ROUND (<input>, <decimal places>)<\/code><\/p>\n\n\n\n
The purpose of the ROUND()<\/code> function is to take a number and round it to the specified number of decimal places to either side of the decimal point<\/em>. ROUND <\/code>can also be used with DATE<\/code> input values in some products.<\/p>\n\n\n\n
As a bit of an aside, in Oracle, you can supply a DATE<\/code> value to this function, as well as the format, and it will round the date to that format. While it is possible, I strongly recommend against it. It\u2019s not standard and it\u2019s rather confusing. Mixing temporal and numeric values makes no sense. What do you think the square root of Ramadan should be? Christmas or Thanksgiving?<\/p>\n\n\n\n
In ANSI Standard SQL, I can use the EXTRACT (<field name> FROM <temporal value>)<\/code> function, where <field name><\/code> is YEAR<\/code>, MONTH<\/code>, DAY, HOUR<\/code>, MINUTE<\/code>, SECOND<\/code> or an interval specification, defined by concatenation of the appropriate temporal field names. Let\u2019s not worry about that right now. }}<\/p>\n\n\n\n
Oracle presents some particular problems. Their data storage model is based on a COBOL PICTURE<\/code> approach to data. That is, they see numbers as strings of digits and not an internal binary representation. This is why they can talk about rounding to the left or right of the decimal point. It is also why they can mix temporal and numeric data in the inputs.<\/p>\n\n\n\n
Instead of following the ISO 8601 standards for temporal data, they use a proprietary display format; This means that \u20192024-01-01\u2019 Is stored as \u201801\/JAN\/24\u2019, so it is mess to sort and depends on English to read. Don\u2019t feel too smug SQL Server users; you have all kinds of options with the non-standard CONVERT()<\/code> function to screw up your dates.<\/p>\n\n\n\n
Oracle also has some weird rules for their BINARY_FLOAT<\/code> and BINARY_DOUBLE<\/code> data type.<\/p>\n\n\n\n
You can use a negative value for the <decimal places><\/code> to get rid of the fractional part, the number and the lower digits. For example, to round number to the hundreds: <\/p>\n\n\n\n
SELECT ROUND(12345.67, -2);<\/pre>\n\n\n\n
Will return 12300.00<\/p>\n\n\n\n
To round to the nearest 5, you use the ROUND<\/code> function and divide your number by 5, then multiply it by 5 . For example: <\/p>\n\n\n\n
SELECT ROUND(3162.845 \/ 5, 0) * 5;<\/pre>\n\n\n\n
Returns 3165, This expression is equal to the following steps: 3162.845 \/ 5.0 = 632.569; ROUND(632.569,0) = 633; 633 * 5 = 3165;<\/p>\n\n\n\n
The action of dividing, rounding, and then multiplying causes the result to be bumped to a multiple of five. Play with this a bit and you can find how to do this for any value not just five.<\/p>\n\n\n\n
Floating-Point Math<\/h2>\n\n\n\n
When I was learning to write FORTRAN, we had to spend two weeks learning how to handle floating-point arithmetic. Since all these values are approximate, the floating-point implementation or hardware is constantly doing rounding under the covers. Unfortunately, this leads to some problems. It means that ((a + b) + c) will not always equal (a + (b + c)) in even simple arithmetic computations. It also means that a test for equality can succeed when it\u2019s not an exact equality. <\/em>In floating-point math two values can be said to be equal, if they differ by less than a certain valued, called epsilon.<\/p>\n\n\n\n
In Standard SQL, we have REAL<\/code>, FLOAT(p)<\/code> and DOUBLE PRECISION<\/code> data types. A lot of programmers today have never had to work with floating-point. Originally, there was a subtle difference between FLOAT<\/code> and REAL<\/code>; At that time, floating-point numbers could be based on a decimal or a binary model for the exponent and mantessa. Every vendor had slightly different implementations. This disappeared with IEEE Standard 754, which uses 32 bits for single precision and 64 bits for double precision.<\/p>\n\n\n\n
But wait, it gets worse! The IEEE model of arithmetic also includes the concept of a NaN<\/code> (not a number). These are bit configurations that hold special symbols. In fact, the IEEE standard even defines rules for floating-point arithmetic with NaN<\/code>s,. Given the problems that people have with NULL<\/code> in SQL, it\u2019s probably not worth it to add more complexity to the language.<\/p>\n\n\n\n
The two interesting NaN<\/code>s for us are -\u221e (negative infinity) and +\u221e (positive infinity), since they could potentially be stored in data. The IEEE Standards set them up as a way to handle overflow problems.<\/p>\n\n\n\n
During the last few decades, software has evolved to handle larger and larger numbers precisely in INTEGER<\/code> and DECIMAL<\/code> data types. Since most databases are commercial and not scientific, there\u2019s little need for floating-point math in the real world. In the last 20 years or so, the only time I have seen someone actually declare a column to be of type REAL, it was a serious design error. They were using it to store currency amounts or measurements that should have been in DECIMAL<\/code>. Given the programmer the benefit of the doubt, I will assume they are old Fortran programmers who simply collapsed back into the keywords they remembered from their original language.<\/p>\n\n\n\n
Conclusion:<\/h2>\n\n\n\n
If you need to do rounding, read the manual for your particular SQL product and maybe take a moment or two to make sure if you are rounding numbers correctly based on your clients needs. It may not be as easy as you think. Make sure that you test for positive, zero and negative numbers as needed, too.<\/p>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
I would hope by now everybody has figured out when you see a price like $39.99 that it might as well be forty bucks. Dropping that penny from the price is done is to fool the consumer! Since we read numbers left to right, the merchant hopes you will see the 3 and assume the……<\/p>\n","protected":false},"author":96214,"featured_media":103544,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,143539],"tags":[159072],"coauthors":[6781],"class_list":["post-103541","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-featured","category-theory-and-design","tag-database-theory"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/103541","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/96214"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=103541"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/103541\/revisions"}],"predecessor-version":[{"id":103549,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/103541\/revisions\/103549"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/103544"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=103541"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=103541"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=103541"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=103541"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}