October 30, 2005 at 8:02 pm
Hi, I'm having a play around and was curious about something.
Very simple select using 2 counts and then dividing them.
The results return 13284, 47357 and 3. Rather than 3 I would have thought the answer would have been 3.5649653718759411.
In order to see the real result I have to wrap converts all over the place:
select count(date_of_birth) as A,
count(*) as B,
convert(float,(convert(float,count(*))/convert(float,count(date_of_birth)))) as C
from myTable
Is there a reason why my "C" is rounded rather than returned as it's true value?
October 30, 2005 at 8:40 pm
Because the count function returns integers, the answer to math using integers is also an integer. Run this script to see the difference:
declare @int1 integer
declare @int2 integer
select @int1 = 10, @int2 = 3
print 'Integers'
print @int1/@int2
declare @float1 float
declare @float2 float
select @float1 = 10, @float2 = 3
print 'Floats'
print @float1/@float2
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
October 30, 2005 at 8:54 pm
Hi and thanks.
I get the bit about count() returning integers because, of course, it is only counting whole units (so to speak). But it is the value of C I don't get.
'C' has not been defined as any data type and only exists within the select statement. Does SQL not recognise that it is ummmmm a variant (probably wrong terminology) and return it's "true" value?
Getting away from using count and to use a simple example:
'select 10/3' results in '3'
Why? this is the bit I don't get.
10/3 is not 3. Why is the result assumed to be an integer unless a convert is put in place?
October 31, 2005 at 12:12 am
integer divide by integer will give u an integer
integer divide by float will give u float
float divide by integer will give u float
select 10 / 3 will give u 3
select 10.0 / 3 or select 10 / 3.0 will give u 3.3333....
October 31, 2005 at 12:39 pm
So I see. Strange behaviour IMHO.
Thanks for explaining. Seems there are many things that can trip you up in SQL.
It's almost as though you have to know the answer prior to asking the question!
Or maybe just know or the potential "gotchas"
November 1, 2005 at 4:58 am
You could also multiply one of the operands by 1.0 to cast it, and hence cast the result...
select count(date_of_birth) as A,
count(*) as B,
count(*)*1.0/count(date_of_birth) as C
from myTable
It's a little easier to read.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply