Select ½ in Mathematical Approach SQL Server Approach

Purpose

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

Introduction

This is a tiny question. However, it is exciting too. A friend asked this question. He recently encountered it in an interview. Immediately I replied to him with the answer "0.5". He said it was a wrong answer and suggested that you execute the same select statement in the SQL Server, and then you can see what happens. After listening to his words, I really wondered and 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 an exciting answer. Any guesses?

Ah! My guess was wrong here, I expected 0.5, but it gave zero (0). I wondered why it answered 0; why not 0.5?

Here we go and consider the following two different approaches:

1. Mathematical approach
2. SQL Server approach

Mathematical approach

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 answered quickly to my friend, but it was wrong. Here my suggestion is to think twice before answering somebody. Sometimes the questions are minimal 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; 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, we concluded that the answer "0.5" was correct.

“½=0.5”

Let's go for the next approach.

SQL Server Approach

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

Let's see the answer if we issue the statement Select ½.

So the answer is Zero (0).

Why did it answer zero? Why not 0.5?

The reason behind this is that 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 about how to proceed to the correct approach; the following are various approaches to achieve the same answer.

I hope you all understand the "Cast and Convert "functions; I will use both in the following scenarios. If anybody doesn't know those two functions, then here you go- Cast and Convert "functions.

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

3. select 1/Convert(float,2) 4.Select convert(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 I have provided in the pictures. We can also understand the problem of selecting ½. Finally, we wrapped that up in this article with a simple solution.

Happy Coding!!!