Round decimal number

Question

Round decimal number

lsalih

SSCertifiable

Points: 6143
More actions
May 13, 2014 at 7:12 am

#396742

Hi
I have the following query, what I like to get is when the result is 1.9 I want the query to round it to 2 and print 2 instead of 1:
declare @val1 int
declare @val2 int
declare @val3 int
declare @avg int
set @val1 = 1.9
set @val2 = 1.9
Set @val3= 1.9
set @avg = convert(decimal,(@val1 + @val2 + @val3) /3 )
print @avg
In above case the OP shows as 1, I wanted to be 2
Thanks.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

The first problem is you're declaring the variables as integer and then assigning a value with a decimal. SQL will truncate this, so set @val1 = 1.9 results in @val1 containing the value 1, because it's an integer and hence can't store 1.9. It truncates, it doesn't round.

Try this

DECLARE @val1 DECIMAL(3,1);

DECLARE @val2 DECIMAL(3,1);

DECLARE @val3 DECIMAL(3,1);

DECLARE @avg INT;

SET @val1 = 1.9;

SET @val2 = 1.9;

SET @val3= 1.9;

SET @avg = ROUND((@val1 + @val2 + @val3)/3 ,0)

PRINT @avg

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

lsalih SSCertifiable Points: 6143 More actions · Answer 2

hi Gail -

Sorry, one correction... I just realized that the user wanted to get the actual decimal result, in this case the result should print 1.9.

declare @val1 int

declare @val2 int

declare @val3 int

declare @avg int

set @val1 = 1.9

set @val2 = 1.9

Set @val3= 1.9

set @avg = convert(decimal,(@val1 + @val2 + @val3) /3 )

print @avg

Meaning, the output should be 1.9, in above query it prints 1

rhythmk SSCertifiable Points: 7271 More actions · Answer 3

As mentioned by Gail first you can not store decimal values in int.So anything after the decimal will not be considered i.e. 1.0 to 1.99 all would give you values as 1 only.

To round any decimal or numeric value.You can use ROUND function.

Please refer below link for more information about it.

Round Function in SQL

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

lsalih (5/13/2014)
hi Gail -
Sorry, one correction... I just realized that the user wanted to get the actual decimal result, in this case the result should print 1.9.

It prints 1 because you are still trying to store decimals in an integer. You cannot store 1.9 in an int variable, it will get truncated (not rounded)

DECLARE @val1 DECIMAL(3,1);

DECLARE @val2 DECIMAL(3,1);

DECLARE @val3 DECIMAL(3,1);

DECLARE @avg DECIMAL(3,1);

SET @val1 = 1.9;

SET @val2 = 1.9;

SET @val3= 1.9;

SET @avg =(@val1 + @val2 + @val3)/3

PRINT @avg

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

rhythmk SSCertifiable Points: 7271 More actions · Answer 5

lsalih (5/13/2014)
hi Gail -
Sorry, one correction... I just realized that the user wanted to get the actual decimal result, in this case the result should print 1.9.
declare @val1 int
declare @val2 int
declare @val3 int
declare @avg int
set @val1 = 1.9
set @val2 = 1.9
Set @val3= 1.9
set @avg = convert(decimal,(@val1 + @val2 + @val3) /3 )
print @avg
Meaning, the output should be 1.9, in above query it prints 1

As mentioned earlier you cant have result as 1.9 as you are trying to store non integer value in int datatype.

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

lsalih SSCertifiable Points: 6143 More actions · Answer 6

Thank you both. So how you correct the query to print 1.9?

rhythmk SSCertifiable Points: 7271 More actions · Answer 7

lsalih (5/13/2014)
Thank you both. So how you correct the query to print 1.9?

Gail has already provided it 🙂

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

lsalih SSCertifiable Points: 6143 More actions · Answer 8

Never mind, I fixed it.. Thank you for the clarification, I declared the values as decimal... That I was not clear about, thank you.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

lsalih (5/13/2014)
Thank you both. So how you correct the query to print 1.9?

Um, maybe using the code I posted?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

lsalih SSCertifiable Points: 6143 More actions · Answer 10

lsalih

SSCertifiable

Points: 6143

May 13, 2014 at 9:04 am

#1713116

Yes, thank you Gail.. You are the best..