CODEWITHSUNDEEP
sql-server-2008
wootscootinboogie
wootscootinboogie

Reputation: 8695

CAST as float with local variable SQL Server 2008

`declare @totalItems int,
@totalPeople int,
@averageThings float;

select @totalItems = count(*) from table1
select @totalpeople = count(*) from tblvisits
select @averageThings = cast(@totalItems/@totalPeople as float)

select @averageThings, @totaPeople, @totalItems

This returns an integer instead of a float, I tried with and without the cast as float and got the same results, how to make it return a float

Upvotes: 0

Views: 5662

Answers (3)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239684

The problem is that you're doing the division in integers, and then converting the result to float. Maybe:

declare @totalItems int,
@totalPeople int,
@averageThings float;

select @totalItems = count(*) from table1
select @totalpeople = count(*) from tblvisits
select @averageThings = @totalItems/cast(@totalPeople as float)

select @averageThings, @totaPeople, @totalItems

To force one of the arguments to be a float before the division occurs, which will then force the division to be done as float instead.

And, as a minor quibble:

This returns an integer instead of a float

No, it returns a float. But one that always happens to have an integer value.

Upvotes: 2

debm
debm

Reputation: 23

You'll need to cast each part

  cast(@totalItems as float)/cast(@totalPeople as float)

Right now it's doing the division with integers and getting an integer result before the cast.

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294287

No, it returns a float. The value of the float may be an integer, but the type is a float. Which also answers your real question: you must perform the division using float operands, not cast the result of int division to float:

declare @totalItems float,
   @totalPeople float,
   @averageThings float;

select @totalItems = count(*) from table1
select @totalpeople = count(*) from tblvisits
select @averagecomorbids = @totalItems/@totalPeople;

select @averageThings, @totaPeople, @totalItems;

Your code will break with divide_by_zero if tblvisits is empty...

Upvotes: 2

Related Questions