May 13, 2014 at 7:12 am
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.
May 13, 2014 at 7:21 am
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
May 13, 2014 at 7:28 am
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
May 13, 2014 at 7:30 am
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.
--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
May 13, 2014 at 7:37 am
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
May 13, 2014 at 7:38 am
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
May 13, 2014 at 7:53 am
Thank you both. So how you correct the query to print 1.9?
May 13, 2014 at 7:57 am
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
May 13, 2014 at 7:58 am
Never mind, I fixed it.. Thank you for the clarification, I declared the values as decimal... That I was not clear about, thank you.
May 13, 2014 at 8:54 am
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
May 13, 2014 at 9:04 am
Yes, thank you Gail.. You are the best..
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy