I have in my select clause:
AVG (cast(scale as decimal(5,2)))
I keep getting a number like: 0.6523412897, nothing I do seems to get me to my desired: 0.65. Basically I want to have a scale of 2 (two decimal places). Thanks
now I have: round (AVG(cast(scale as decimal)),2), my result is 1.95000.....how do I get rid of the trailing zeros, so I just get 1.95?
bmw0128
2010-01-08 22:08:03
+3
A:
Cast the average, don't average the cast:
cast(AVG(scale) as decimal(5,2))
Update
Using ROUND() changes the value not the type. Eg. select round(0.12345,2) returns 0.12000 as it should becuase it rounds 0.12345 to 0.12 but keeps the original type with 5 decimals after the point, hence 0.12000. To change the type one must use cast, as in my post.
Remus Rusanu
2010-01-08 21:53:26
if i change to: cast(AVG(scale) as decimal(5,2)), i lose precision, for the numbers i am averaging are integers, so the result is of this is 1.00, 2.00, etc.
bmw0128
2010-01-08 22:26:30
figured it out, double cast is required...cast(AVG (cast(scale as decimal(5,2))) as decimal(5,2)) as [AVG]
bmw0128
2010-01-08 22:33:15