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

Possible duplicate of [How to get a float result by dividing two integer values?](https://stackoverflow.com/questions/11719044/how-to-get-a-float-result-by-dividing-two-integer-values) — Peter B, Mar 13 '19 at 20:57
Peter B, Agreed, duplicate, just did not know I was having a float problem, thought it was the expression complexity because it "worked" when I broke it into step. Really just did a conversion after the first set and got me into float territory. — Jim Melcher, Mar 13 '19 at 21:08
Its weird how you accepted the most simple answer that doesn't contains any clarification about the cause since you are asking **why `(805499 / 847125) * 100` returned `0` !!!!** you didn't asked how to do a workaround. — Yahfoufi, Mar 14 '19 at 15:27

Zaynul Abadin Tuhin · Answer 1 · 2019-03-13T21:03:12.623

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

score 0 · Accepted Answer · answered Mar 13 '19 at 20:53

0

this is working for me: Try add .00 in the set value.

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

answered Mar 13 '19 at 20:53

Hasan Mahmood

978
7
10

You got it, Sorry for being foolish about my CAST. This works: – Jim Melcher Mar 13 '19 at 20:58
2

Give the guy time to evaluate your solution (and others); don't beg people to mark your answer as the answer within 5 minutes. – Aaron Bertrand Mar 13 '19 at 21:02

score 0 · Answer 3 · edited Jun 20 '20 at 09:12

If you use the following statement

SET @HitRate = (805499 / 847125) * 100

It will return 0 since 805499 and 847125 are integer so the result of the division is integer = 0.

Based on the TSQL division official documentation:

Returns the data type of the argument with the higher precedence.

If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated.

You can check the SQL data types precedence list from the link below:

Data type precedence (Transact-SQL)

But when you use :

DECLARE @HitRate real
SET @HitRate = 805499
SET @HitRate = (@HitRate / 847125) * 100
SELECT @HitRate

It will return 95.0862 since @HitRate is of type real and it has higher precedence than integer

Agreed. If I "flip it" {SET @HitRate = (847125 / 805499)} I get 1 — Jim Melcher, Mar 13 '19 at 21:03

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

3 Answers3