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

# re: Beware of Implicit conversions

Thursday, January 17, 2008 2:25 PM 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

Thursday, January 17, 2008 2:36 PM 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 &laquo; Karuta&#8217;s ASP &amp; M\$ SQLserver

Pingback from  execute mathematical formula stored as string &laquo; Karuta&#8217;s ASP &amp; M\$ SQLserver