Tuesday, March 20, 2012

Could somone clarify SQL division and explain the examples, thanks in advance!

Code Snippet

SELECT 3308 / 15104

The above division will result with: 0

Code Snippet

SELECT 3308 / (15104 * 1)

The above division will result with: 0

Code Snippet

SELECT 3308 / (15104 * 1.0)

The above division will result with: 0.219014830

MY MAIN GOAL is to produce a result of: 0.2

I was thinking of using ROUND, then with some combination of RIGHT. Your explanation and advice is greatly appreciated! Thanks again!

You need to give a read to the various datatypes in books online. Start with the NUMERIC and DECIMAL datatypes.

The first two examples are both examples of INTEGER division. In all cases the results are truncated to the closest lower integer.

In the second example the results of (15104 * 1.0) are converted to FLOAT datatype because of the "1.0" piece. Next, 3308 is converted to a float datatype because the intermediate is a float and this causes the result to be a float type.

For a DECIMAL example:

Code Snippet

SELECT cast(3308 / (15104 * 1.0) as decimal (9,1))
as [1-decimal result]

/*
1-decimal result
-
.2
*/

|||

I did read up on the various datatypes prior to posting, but I don't remember reading anything on math equations and how SQL handles them.

Thanks for the explanation. I'll do further research on the internet to learn more.

sql

No comments:

Post a Comment