Getting my head around this

Question

Getting my head around this

QPR

SSC Eights!

Points: 814
More actions
October 30, 2005 at 8:02 pm

#90698

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?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 1

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

QPR SSC Eights! Points: 814 More actions · Answer 2

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?

[KH] SSCommitted Points: 1908 More actions · Answer 3

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....

QPR SSC Eights! Points: 814 More actions · Answer 4

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"

AndyLoughman SSC-Addicted Points: 422 More actions · Answer 5

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.