Arithmetic rounding
-
Hi I need to multiply two columns (float) each of which can have a max of 4 decimal places and round the result to 3 decimal places. It seems the ROUND(val,decimal_places) uses bankers rounding? (SQL Sever 2008R2) - Although I have read it uses Symmetric Arithmetic Rounding? Doing the same calculation in Excel gives me the result I need, but Excel uses normal Arithmetic rounding. How can I do a query to use arithmetic rounding (like Excel) Examples of the errors:
Price Qty Val ExcelRound SQLRound
7.5169 745 5600.0905 5600.091 5600.090
4.3465 463 2012.4295 2012.430 2012.429
1.6401 125 205.0125 205.013 205.012
0.0395 369 14.5755 14.576 14.575
5.2349 955 4999.3295 4999.330 4999.329
9.0285 141 1273.0185 1273.019 1273.018
9.0899 645 5862.9855 5862.986 5862.985
3.6167 215 777.5905 777.591 777.590
3.1145 135 420.4575 420.458 420.457
7.4115 105 778.2075 778.208 778.207
7.8675 313 2462.5275 2462.528 2462.527
8.8405 227 2006.7935 2006.794 2006.793
5.4269 55 298.4795 298.480 298.479
1.8833 445 838.0685 838.069 838.068
9.7349 655 6376.3595 6376.360 6376.359
8.6487 365 3156.7755 3156.776 3156.775
1.9033 125 237.9125 237.913 237.912
4.8197 545 2626.7365 2626.737 2626.736PS: how do you post a table with nicely formatted columns?
-
Hi I need to multiply two columns (float) each of which can have a max of 4 decimal places and round the result to 3 decimal places. It seems the ROUND(val,decimal_places) uses bankers rounding? (SQL Sever 2008R2) - Although I have read it uses Symmetric Arithmetic Rounding? Doing the same calculation in Excel gives me the result I need, but Excel uses normal Arithmetic rounding. How can I do a query to use arithmetic rounding (like Excel) Examples of the errors:
Price Qty Val ExcelRound SQLRound
7.5169 745 5600.0905 5600.091 5600.090
4.3465 463 2012.4295 2012.430 2012.429
1.6401 125 205.0125 205.013 205.012
0.0395 369 14.5755 14.576 14.575
5.2349 955 4999.3295 4999.330 4999.329
9.0285 141 1273.0185 1273.019 1273.018
9.0899 645 5862.9855 5862.986 5862.985
3.6167 215 777.5905 777.591 777.590
3.1145 135 420.4575 420.458 420.457
7.4115 105 778.2075 778.208 778.207
7.8675 313 2462.5275 2462.528 2462.527
8.8405 227 2006.7935 2006.794 2006.793
5.4269 55 298.4795 298.480 298.479
1.8833 445 838.0685 838.069 838.068
9.7349 655 6376.3595 6376.360 6376.359
8.6487 365 3156.7755 3156.776 3156.775
1.9033 125 237.9125 237.913 237.912
4.8197 545 2626.7365 2626.737 2626.736PS: how do you post a table with nicely formatted columns?
I think the general consensus is that it makes more sense to do that kind of formatting in your report, rather than in your database query.
The difficult we do right away... ...the impossible takes slightly longer.
-
Hi I need to multiply two columns (float) each of which can have a max of 4 decimal places and round the result to 3 decimal places. It seems the ROUND(val,decimal_places) uses bankers rounding? (SQL Sever 2008R2) - Although I have read it uses Symmetric Arithmetic Rounding? Doing the same calculation in Excel gives me the result I need, but Excel uses normal Arithmetic rounding. How can I do a query to use arithmetic rounding (like Excel) Examples of the errors:
Price Qty Val ExcelRound SQLRound
7.5169 745 5600.0905 5600.091 5600.090
4.3465 463 2012.4295 2012.430 2012.429
1.6401 125 205.0125 205.013 205.012
0.0395 369 14.5755 14.576 14.575
5.2349 955 4999.3295 4999.330 4999.329
9.0285 141 1273.0185 1273.019 1273.018
9.0899 645 5862.9855 5862.986 5862.985
3.6167 215 777.5905 777.591 777.590
3.1145 135 420.4575 420.458 420.457
7.4115 105 778.2075 778.208 778.207
7.8675 313 2462.5275 2462.528 2462.527
8.8405 227 2006.7935 2006.794 2006.793
5.4269 55 298.4795 298.480 298.479
1.8833 445 838.0685 838.069 838.068
9.7349 655 6376.3595 6376.360 6376.359
8.6487 365 3156.7755 3156.776 3156.775
1.9033 125 237.9125 237.913 237.912
4.8197 545 2626.7365 2626.737 2626.736PS: how do you post a table with nicely formatted columns?
Hi, The problem is not a ROUND function, but the precision of the FLOAT data type (Using decimal, float, and real Data[^]). You have at least a couple of options: 1. Use DECIMAL or NUMERIC instead of the FLOAT. 2. CAST to DECIMAL for the calculation. Here's the demonstration for you (using your first example): 1. Select using ROUND.
SELECT ROUND((7.5169 * 745), 3) AS Result;
or
SELECT ROUND(5600.0905, 3) AS Result;
Result: 5600.0910 2. Using FLOAT variables.
DECLARE @a FLOAT, @b FLOAT, @c FLOAT;
SET @a = 7.5169;
SET @b = 745;
SET @c = @a * @b;
SELECT ROUND(@c, 3) AS Result;Result: 5600.09 3. Using DECIMAL variables.
DECLARE @a DECIMAL(18, 4), @b DECIMAL(18, 4), @c DECIMAL(18, 4);
SET @a = 7.5169;
SET @b = 745;
SET @c = @a * @b;
SELECT ROUND(@c, 3) AS Result;Result: 5600.0910 P.S. I am posting such data with the XML pre tag. Also, I am inserting (copying) TAB characters (if needed).
Best regards, Andrius Leonavicius
-
Hi, The problem is not a ROUND function, but the precision of the FLOAT data type (Using decimal, float, and real Data[^]). You have at least a couple of options: 1. Use DECIMAL or NUMERIC instead of the FLOAT. 2. CAST to DECIMAL for the calculation. Here's the demonstration for you (using your first example): 1. Select using ROUND.
SELECT ROUND((7.5169 * 745), 3) AS Result;
or
SELECT ROUND(5600.0905, 3) AS Result;
Result: 5600.0910 2. Using FLOAT variables.
DECLARE @a FLOAT, @b FLOAT, @c FLOAT;
SET @a = 7.5169;
SET @b = 745;
SET @c = @a * @b;
SELECT ROUND(@c, 3) AS Result;Result: 5600.09 3. Using DECIMAL variables.
DECLARE @a DECIMAL(18, 4), @b DECIMAL(18, 4), @c DECIMAL(18, 4);
SET @a = 7.5169;
SET @b = 745;
SET @c = @a * @b;
SELECT ROUND(@c, 3) AS Result;Result: 5600.0910 P.S. I am posting such data with the XML pre tag. Also, I am inserting (copying) TAB characters (if needed).
Best regards, Andrius Leonavicius
Hi Andrius Thanks So much. Tested below with the same random sample of 300 values, and all agreed with the Excel Values!
select price
, qty
, price*qty as Val
, round(CONVERT(decimal(12,4),price)* CONVERT(decimal(12,4),qty),3) as RoundVal
from dbo.[round]What mis-lead me into thinking the problem was with the type of rounding was that from my sample of 300 values, all the values that differed had a 5 in the 4th decimal place, and all the errors were not rounded as expected. In the link you poseted: Using float and real Data The float and real data types are known as approximate data types. The behavior of float and real follows the IEEE 754 specification on approximate numeric data types. ... The IEEE 754 specification provides four rounding modes: round to nearest, round up, round down, and round to zero. Microsoft SQL Server uses round up. All are accurate to the guaranteed precision but can result in slightly different floating-point values. Because the binary representation of a floating-point number may use one of many legal rounding schemes, it is impossible to reliably quantify a floating-point value. Thanks for your help!
-
I think the general consensus is that it makes more sense to do that kind of formatting in your report, rather than in your database query.
The difficult we do right away... ...the impossible takes slightly longer.
Hi Richard Thanks for your reply. In my case here, a customer was requesting a Purchase Order in pdf format, as well as Excel format. I was doing the rounding with a formula in Excel, but the formula did not always fill down to all the rows of the table which is why I decided to do the rounding in the query. Also, quite often, in Winform apps, I pull a query into a DataTable, and set the DataTable as a DataGridView DataSource, in which case it seems more practical to do the rounding in the query as opposed to looping through the DataTable, and adding the rows with the rounded value to the DataGridView?
-
Hi Andrius Thanks So much. Tested below with the same random sample of 300 values, and all agreed with the Excel Values!
select price
, qty
, price*qty as Val
, round(CONVERT(decimal(12,4),price)* CONVERT(decimal(12,4),qty),3) as RoundVal
from dbo.[round]What mis-lead me into thinking the problem was with the type of rounding was that from my sample of 300 values, all the values that differed had a 5 in the 4th decimal place, and all the errors were not rounded as expected. In the link you poseted: Using float and real Data The float and real data types are known as approximate data types. The behavior of float and real follows the IEEE 754 specification on approximate numeric data types. ... The IEEE 754 specification provides four rounding modes: round to nearest, round up, round down, and round to zero. Microsoft SQL Server uses round up. All are accurate to the guaranteed precision but can result in slightly different floating-point values. Because the binary representation of a floating-point number may use one of many legal rounding schemes, it is impossible to reliably quantify a floating-point value. Thanks for your help!
You're welcome. :)
Best regards, Andrius Leonavicius
