I was recently working on some old code that performed some basic calculations in a sql query when I noticed that a number I expected to be a decimal value was sometimes coming out as a whole number even though the correct value should have a decimal component.

The problem was that both the numerator and the denominator were whole numbers. This is where SQL Server shows how dumb it can be sometimes. Since both numbers where integers (even though the column the numerator value came from was defined as a float) it tried to convert the result to an integer as well.

This problem shows up when you are performing mathematical operations such as division or averaging on all whole numbers. To illustrate run this simple query.

select
      (1/2) as result

Result: 0

You would expect the result to be "0.5" but what we actually get is "0".

The solution to this is to CAST or CONVERT the numerator to a float in the query so that SQL Server does not round the result to a whole number.

Solution 1: Use CAST

select
      (
            CAST(1 as float)/2
      ) as result

Result: 0.5

Solution 2: Use CONVERT

select
      (
            CONVERT(FLOAT,1)/2
      ) as result

Result: 0.5

Solution 3: Multiply By 1.0

I came across this solution a few years ago on a forum. By multiplying the numerator by 1.0 we again get a float. However this time instead of getting "0.5" we get "0.500000". I believe that this is because sql server expects the result to be of type 'decimal' and not 'float'. In most cases this doesn't matter anyway but its good to be aware of the difference.

select
      (
            (1 * 1.0)/2
      ) as result

Result: 0.500000

A Note On MySQL

I have not seen the behavior on my development copy of MySQL (5.5.11), but I cannot say definitively that it does not exist of older versions...though I would be surprised if it did.