August 3, 2016 at 1:48 pm
Hi ALL,
I have a situation where I should round a col in a table
Ex
Units Desired Result
0.3 0.3
0.1 0.1
1.4 1.4
1.5 2
1.7 2
0.8 1
I tried round(units,2) and also round(units,0) but in both cases I do not get the desired result
Thanks in advance
August 3, 2016 at 1:55 pm
ntreelevel (8/3/2016)
Hi ALL,I have a situation where I should round a col in a table
Ex
Units Desired Result
0.3 0.3
0.1 0.1
1.4 1.4
1.5 2
1.7 2
0.8 1
I tried round(units,2) and also round(units,0) but in both cases I do not get the desired result
Thanks in advance
Maybe something like this:
CREATE TABLE #Test(
Units decimal(10,5),
DesiredResult decimal(10,5),
);
INSERT INTO #Test SELECT
0.3, 0.3 UNION ALL SELECT
0.1, 0.1 UNION ALL SELECT
1.4, 1.4 UNION ALL SELECT
1.5, 2 UNION ALL SELECT
1.7, 2 UNION ALL SELECT
0.8, 1;
SELECT *,
CASE WHEN ROUND(Units,0) = ROUND(Units,0,1)
THEN Units ELSE ROUND(Units,0)
END
FROM #Test
GO
DROP TABLE #Test
August 15, 2016 at 10:12 am
The ANSI/ISO standards define rounding as "implementation defined", so you have to be careful. Google scientific versus commercial rounding (I prefer this because it is more "set oriented" than "single value").
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
August 15, 2016 at 3:45 pm
CELKO (8/15/2016)
The ANSI/ISO standards define rounding as "implementation defined", so you have to be careful. Google scientific versus commercial rounding (I prefer this because it is more "set oriented" than "single value").
Anyone for a discussion on "Bankers Rounding"? ;-):-P:-D:-)
--Jeff Moden
Change is inevitable... Change for the better is not.
August 15, 2016 at 3:57 pm
Me! Me! 😛 But which one? There have been:
1) go up or down from "x5" alternating.
2) go up or down from "x5" in a uniform random distribution.
3) go up or down from "x5" based on a Bayesian algorithm
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
August 16, 2016 at 5:42 am
Hi all, Thank you for the answers but I figured out that using a case statements for custom rounding is the best way and it worked out
August 16, 2016 at 6:50 am
ntreelevel (8/16/2016)
Hi all, Thank you for the answers but I figured out that using a case statements for custom rounding is the best way and it worked out
Do you mean like what I posted? Or something else? If it's something else, would you mind sharing with everyone?
Excuse Jeff and Joe. Rounding is more complicated than it seems and they have a lot of experience that they want to share. It might not look relevant to your problem, but it could certainly be interesting.
August 16, 2016 at 8:18 am
CELKO (8/15/2016)
Me! Me! 😛 But which one? There have been:1) go up or down from "x5" alternating.
2) go up or down from "x5" in a uniform random distribution.
3) go up or down from "x5" based on a Bayesian algorithm
No, please. The last time we ended up with two threads and a lot flames.
August 16, 2016 at 10:25 am
Jeff Moden (8/15/2016)
CELKO (8/15/2016)
The ANSI/ISO standards define rounding as "implementation defined", so you have to be careful. Google scientific versus commercial rounding (I prefer this because it is more "set oriented" than "single value").Anyone for a discussion on "Bankers Rounding"? ;-):-P:-D:-)
hahaha, yes the default methodology of the ROUND function of the old VB6... which didn't match what Excel did by default. Learned about rounding algorithms that day I did. 🙂
August 16, 2016 at 3:54 pm
I used to be a statistician in a former life, but I do not remember ever seeing a really good book/paper/magazine article on all the problems of rounding! Usually all we got was a little note about scientific versus commercial and that was it!
Anyone know of such a thing, that goes into a little more theory and justification than just telling us how this particular product or industry does things?
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
August 16, 2016 at 8:13 pm
When I started writing code as far back as 1969, the accepted method was to add 5 to one place to the right of the desired result length and then truncate. Thus, for example, to round to cents ( two decimal places ), we added .005 (three decimal places) to any number of decimals and then truncated. In SQL you can do that fairly easily if you variables are defined correctly.
123.4567 123.4467
.0050 .0050
----------- -----------
123.4617 123.4517
when truncated, become:
123.46 123.45
Can't get the columns to align here , but you can get the idea anyway
Rick
Disaster Recovery = Backup ( Backup ( Your Backup ) )
August 16, 2016 at 10:55 pm
ntreelevel (8/16/2016)
Hi all, Thank you for the answers but I figured out that using a case statements for custom rounding is the best way and it worked out
Would you post your code, please? It might be a help to others that happen upon this thread. At the very least, I'm interested in seeing what you came up with.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 17, 2016 at 11:20 am
skeleton567 (8/16/2016)
When I started writing code as far back as 1969, the accepted method was to add 5 to one place to the right of the desired result length and then truncate. Thus, for example, to round to cents ( two decimal places ), we added .005 (three decimal places) to any number of decimals and then truncated. In SQL you can do that fairly easily if you variables are defined correctly.123.4567 123.4467
.0050 .0050
----------- -----------
123.4617 123.4517
when truncated, become:
123.46 123.45
Can't get the columns to align here , but you can get the idea anyway
This was the traditional scientific method. The traditional commercial method was to look for things at five and alternately round them up or down, as the sequential processing of a deck of punch cards or Mac tape pass through your accounting system. The assumption was that, on the average, any error would be as likely to go up or down so this method would not skew in one direction or another.
Then we found out – aargh! – That some processes are skewed by nature. This meant we had to un-skew them when we got the data and put it in the database. This is when we started using beige and statistics in the rounding. In 25 words or less, the beige and model is to take a sample and assume that it is representative of the whole population. Then we use feedback to correct things that were looking at ("the best predictor of future behavior is past behavior" – statisticians proverb).
This gets even worse with floating-point math. One of the things I tell people to do in one of my books is to generate a column of random positive and negative floating-point numbers, and to sum() on it. Then sort it from high to low and re-add it; sort from low to high; sort the absolute values from low to high; sort the absolute values from high to low. Now look at the results and pay attention to the last decimal places. They will all be a little different.
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
August 17, 2016 at 11:25 am
This is the custom case statement that I used
case when (units - floor(units)) < 0.49 then round(units,2)
when (units - floor(units)) > = 0.50 then round(units,0)
end
August 17, 2016 at 11:35 am
ntreelevel (8/17/2016)
This is the custom case statement that I usedcase when (units - floor(units)) < 0.49 then round(units,2)
when (units - floor(units)) > = 0.50 then round(units,0)
end
You're missing values. If you have a value like N.49 it will return a NULL value.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply