Beware of Implicit conversions

Sometimes you may wonder why integer divisions are not giving exact result

Consider this example

Select 3/4

Select 4/3

Because both numerator and denominators are INTegers, results 0.75, 1.3333 are truncated to integers resulting 0 and 1 respectively

Suppose you want to find out percentage number of orders taken for each shipcity against total orders


select shipcity,count(*)/(select count(*) from northwind..orders)*100  as percentage

from northwind..orders

group by shipcity

The result of percentage column is 0

To avoid this, you need to convert one of the openrands by float ,decimal or multiply by 1.0

Select 3*1.0/4

Select 4*1.0/3


select shipcity,count(*)*1.0/(select count(*) from northwind..orders)*100 as percentage

from northwind..orders

group by shipcity

Published 16 January 2008 10:51 by Madhivanan

Comments

# re: Beware of Implicit conversions

17 January 2008 14:25 by rudy

percentage was perhaps not the best example, because you don't really need that extra 1.0 factor in there

100.0 * count(*) / (select count(*) from northwind..orders) as percentage

# re: Beware of Implicit conversions

17 January 2008 14:36 by Madhivanan

Hi Rudy

Yes it is. I just showed it as an example

If variables are used, then it would be useful to multiply it by 1.0

Select @var1/@var2+@var3

to

Select @var1*1.0/@var2+@var3

# execute mathematical formula stored as string « Karuta’s ASP & M$ SQLserver

Pingback from  execute mathematical formula stored as string « Karuta’s ASP & M$ SQLserver