Beware of Implicit conversions

Published 16 January 08 12:42 PM | Madhivanan 

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

Comments

# Other SQL Server Blogs around the Web said on January 16, 2008 7:55 AM:

Sometimes you may wonder why integer divisions are not giving exact result Consider this example Select

# SSQA.net - SqlServer-QA.net said on January 16, 2008 8:28 AM:

Sometimes you may wonder why integer divisions are not giving exact result Consider this example Select

Anonymous comments are disabled