## 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
Wednesday, January 16, 2008 10:51 AM
by
Madhivanan

Filed under: sql server, T-sql, Implicit conversion

## # re: Beware of Implicit conversions

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

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