CODEWITHSUNDEEP
sqlsql-server
Nitendra Jain
Nitendra Jain

Reputation: 499

Why is this difference in output ?

select ROUND(5.8990,0) output is 6.000

select ROUND(643/109,0) output is 5

Why is this difference in output ?

Upvotes: 3

Views: 94

Answers (7)

Mahmoud Ali
Mahmoud Ali

Reputation: 1379

In the second case, you are dividing two integers, so you get an integer as a result. The result of the division is 5.89, but it will just discard any remainders because it needs an integer as return, so that gives you 5. The result is truncated. More info

If you want to have a decimal result, just use a decimal point number in any of the sides of the division, like

select ROUND(643.0/109,0) -- returns 6
-- or
select ROUND(643/109.0,0) -- also returns 6

In the first case, ROUND will not truncate the result, it will round to the next closer integer (according to the parameters you provided). You can get the same result if you use

select ROUND(5.8990, 0, 1) 
-- returns 5

More info on ROUND here

Upvotes: 0

knkarthick24
knkarthick24

Reputation: 3216

select 643/109 gives 5 quotient . So select ROUND(5,0) is 5

Upvotes: 0

SanyTiger
SanyTiger

Reputation: 666

In your first query, select ROUND(5.8990,0) the output is 6.000 as the value, 5.8990, passed is assumed to be decimal due to presence of the point. But in the case of the second query, select ROUND(643/109,0) 643 and 109 are assumed to be integers, so the value returned is 5, not 5.8990 as you were expecting. In order to change get the output as 6.000, all you have to do is pass the second query like this :

select ROUND(643.00/109.00,0)

enter image description here

Upvotes: 0

Sai Kalyan Kumar Akshinthala
Sai Kalyan Kumar Akshinthala

Reputation: 11844

If you want to get Decimal part also you should use float or decimal, refer below code once

select ROUND(CONVERT(Decimal(18,2),643)/CONVERT(Decimal(18,2),109),0) output is 6.000

Upvotes: 0

Ujjwal Kumar
Ujjwal Kumar

Reputation: 581

A workaround would be to just add decimal point to any of the numbers. Then the parameter to the round function would be a decimal and not an integer.

Upvotes: 0

mhawke
mhawke

Reputation: 87064

It's due to use of integer division.

> select 643/109;
5
> select round(643/109, 0);
5.0
> select 643/109.0;
5.89908256880734
> select round(643/109.0, 0);
6.0

The first command shows that integer division is taking place. So, change the divisor or dividend to a float and you will get your expected result as shown in the last command.

Upvotes: 2

Deepak Mishra
Deepak Mishra

Reputation: 3183

This is because int/int returns the int part only.

Try this:

select Round(Cast(643 as decimal)/cast(109 as decimal),0)

Upvotes: 0

Related Questions