Select ½ in Mathematical Approach SQL Server Approach

Purpose

The main purose of this article is is to show how to get non-integer values in SQL Server.

Description

This is a very small question however it is very interesting too. This question was asked by a friend. He recently encountered it in an interview. Immediately I replied to him with the answer “0.5”. He said that it was a wrong answer and suggested that you can execute the same select statement in the SQL Server and then you can see what happens. I then really wondered, after listening to his words, and then I could not control my eagerness; finally I opened my system and executed the same select statement in my SQL Server machine. Here the SQL Server had given a very interesting answer. Any guesses?

Ah! My guess was wrong here, I expected 0.5 but it gave zero (0) . I really wondered why it gave the answer as Zero, why not 0.5?

Here we go and consider the following two different approaches:

1. Mathematical approach
2. SQL Server approach

1. Mathematical approach

I think everybody should understand the arithmetical calculations from their first or second grade onword; I also was in that category. This is the only reason I gave an answer very quickly to my friend, but it was a wrong answer. Here my suggestion is to think twice before giving an answer to somebody. Sometimes the questions are very small but tricky. I encountered the same situation here. If we consider the normal (1/2) the answer is 0.5 only. Here we can't do the calculation manually, right, we have issued a select statement to the database engine. So it will do the calculation then it will give you the output. So finally in this mathematical approach what we concluded is the answer “0.5” was correct.

“½=0.5”

Let's go for the next approach.

2. SQL Server Approach

Here we can see a different answer if we issue a select statement to the database engine.

Let's see practically what the answer is if we issue the statement Select ½?



So the answer is Zero (0).

Why did it give the answer zero, why not 0.5?

The reason behind this is, here the SQL Server considers 1 and 2 as integers so if we issue a select statement as in the preceding then by default it will consider those values as integers. This is likely because SQL Server is using integer arithmetic for the calculation. The integer part of ½ is 0.

This is only the reason we get the output as 0. But it was the wrong answer.

So now we think, how to proceed to the correct approach, the following are various approaches to achieve the same answer.

I hope you all understand the use of “Cast and Convert “functions, here I'm going to use both the functions in the following scenarios. If anybody doesn't have an idea of those two functions then here you go http://msdn.microsoft.com/en-IN/library/ms187928.aspx

1.Select 1.0/2.0 as output 2. select convert(float,1)/2 as output



3.select 1/Convert(float,2) 4.Selectconvert(float,1)/Convert(float,2)



5. select 1/Convert(float,2) 6. select CAST(1 as float)/cast(2 as float)



7. select CAST(1 as float)/cast(2 as float) 8. select 1/cast(2 as float)




9. SELECT CONVERT(NUMERIC(5, 2), 1.0/2.0)as output



Summary

This is the end of the article, I hope you all understand the various ways of approaching the same answer by observing the preceding examples that I have provided in the pictures and we can also understand the problem of select ½ and finally we wrapped that up in this article with a simple solution.

Happy Coding!!!