February 13, 2014 at 8:48 am
I am using CAST to convert into decimal and find the percentage, but it rounds the value to the nearest int.
I want to see the exact(2) decimal value...
Here is the current syntax:
Cast(Cast(Other_Prods as Decimal(18,2))/Cast(Total_Prods as Decimal(18,2))as Decimal(18,2))*100 PercentageProds
Current ouput is 11 ,9,5
I am looking for 10.99,9.20,4.85
If I try to do : Cast(Other_Prods as Decimal(18,2))/Cast(Total_Prods as Decimal(18,2))* 100 PercentageProds
I get 10.989925555 but I am only looking for 2 values after decimal without rounding
February 13, 2014 at 9:03 am
sharonsql2013 (2/13/2014)
I am using CAST to convert into decimal and find the percentage, but it rounds the value to the nearest int.I want to see the exact(2) decimal value...
Here is the current syntax:
Cast(Cast(Other_Prods as Decimal(18,2))/Cast(Total_Prods as Decimal(18,2))as Decimal(18,2))*100 PercentageProds
Current ouput is 11 ,9,5
I am looking for 10.99,9.20,4.85
If I try to do : Cast(Other_Prods as Decimal(18,2))/Cast(Total_Prods as Decimal(18,2))* 100 PercentageProds
I get 10.989925555 but I am only looking for 2 values after decimal without rounding
If I take the two bits of code you've posted and execute them against some data, this is what I get: -
SELECT Other_Prods, Total_Prods,
CAST(CAST(Other_Prods AS DECIMAL(18, 2)) / CAST(Total_Prods AS DECIMAL(18, 2)) AS DECIMAL(18, 2)) * 100,
CAST(Other_Prods AS DECIMAL(18, 2)) / CAST(Total_Prods AS DECIMAL(18, 2)) * 100
FROM ( VALUES ( 33, 191), ( 72, 75) ) a ( Other_Prods, Total_Prods );
Other_Prods Total_Prods
----------- ----------- --------------------------------------- ---------------------------------------
33 191 17.00 17.2774869109947644
72 75 96.00 96.0000000000000000
So if I take that to the logical conclusion that you were heading towards, we just add one more cast: -
SELECT Other_Prods, Total_Prods,
CAST(CAST(Other_Prods AS DECIMAL(18, 2)) / CAST(Total_Prods AS DECIMAL(18, 2)) AS DECIMAL(18, 2)) * 100,
CAST(Other_Prods AS DECIMAL(18, 2)) / CAST(Total_Prods AS DECIMAL(18, 2)) * 100,
CAST(CAST(Other_Prods AS DECIMAL(18, 2)) / CAST(Total_Prods AS DECIMAL(18, 2)) * 100 AS DECIMAL(18, 2))
FROM ( VALUES ( 33, 191), ( 72, 75) ) a ( Other_Prods, Total_Prods );
Other_Prods Total_Prods
----------- ----------- --------------------------------------- --------------------------------------- ---------------------------------------
33 191 17.00 17.2774869109947644 17.28
72 75 96.00 96.0000000000000000 96.00
But, I don't think this is actually the way you want to do this. Instead, I think that this is probably cleaner: -
SELECT Other_Prods, Total_Prods,
CAST(100.00 * Other_Prods / Total_Prods AS DECIMAL(18, 2))
FROM ( VALUES ( 33, 191), ( 72, 75) ) a ( Other_Prods, Total_Prods );
Other_Prods Total_Prods
----------- ----------- ---------------------------------------
33 191 17.28
72 75 96.00
February 13, 2014 at 9:14 am
That does help.
Thank you so much
November 25, 2014 at 9:31 am
I feel really stupid that I cannot figure out this CAST issue. I have a situation where I need some records in a column to be INT & some to be DECIMAL. (Some need a decimal display and others don't within the same column.) I thought I'd figured it out by casting the int to decimal and then casting it again to NVARCHAR. That works to allow me to have the mix, but it's not rounding the DECIMAL correctly.
I need this to return 9.5. It keeps returning 9.0.
declare @int int=9500
select cast(@int/1000 as decimal(9,1))
November 25, 2014 at 9:35 am
Ok, this works, but it's the ugliest thing I've ever seen. Surely, there is a better way. Keep in mind I'm trying to get the 9.5 to an NVARCHAR because I've got INT values in the same column & I want some to display with a decimal and others not to.
declare @int int=9500
select CAST(cast(cast(@int as decimal(9,1))/1000 as decimal(9,1)) as NVARCHAR(100))
The entire original column was INT. Only one field needs a decimal. I converted both to NVARCHAR() to accomplish it, but it is just so ugly.
So, I need things like this:
1
2
3.5
4
5
November 25, 2014 at 10:19 am
What does this return?
select cast(@int/1000.00 as decimal(9,1))
Dividing by 1000 may be forcing it to do integer math.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply