Assign quotient or longer expression to local (real) variable T SQL

Question

Was floored to learn this, surely I am making and error...

If I DECLARE a real (or decimal) local variable I can't assign (SET) the value of an expression? I have to set to a value first then step through the expression. If I try to "do the math" all at once I get 0.

So, this works..

DECLARE @HitRate real
SET @HitRate = 805499
SET @HitRate = (@HitRate / 847125) * 100
--SET @HitRate = (805499 / 847125) * 100  --But this does not work?
SELECT @HitRate

• If I comment out the first 2 SET statement and just use the the third I get 0
• If I use all 3, I get 0
• If I CAST(@HitRate As DECIMAL(9,2)) I get 0

Answer 1

demoyou need to make that 805499 to 805499.00 that means int to float

DECLARE @HitRate real
SET @HitRate = 805499
SET @HitRate = (@HitRate / 847125) * 100
SET @HitRate = (805499.00 / 847125) * 100.00  --this will  work now
SELECT @HitRate

you got 0 because, 805499/847125 = 0.95 but db engine return 0 because it takes it is an integer value that's why when you multiply 100 with 0 it also make output 0

or you could explicitly cast like below

SET @HitRate = (cast( 805499 as float) / 847125) * 100 

it will also return 95.0862