Division of integers in MS SQL

Posted by Dan on Nov 4, 2008 @ 10:52 AM

Yesterday I was trying to divide two values to calculate a percentage, but the value was always coming up zero. What really threw me off was I had a query similar to the following and that was working fine:

select
    floor((avg(Rating) / count(Rating)) * 100) as [Percentage]
from
    Ratings

The above query was properly returning the percentages I needed, but when I had a query like the following, all of the sudden it was just returning 0 (zero) as the result—which I knew was wrong:

select
    count(1) / (select count(1) from Sales where datediff(dd, OrderDate , getutcdate()) = 0 ) as SameDayShipPct
from
    Sales
where
     datediff(dd, OrderDate , getutcdate()) = 0
and
    datediff(dd, ShipDate , getutcdate()) = 0

The reason the above query kept returning zero, was because the SQL COUNT() function returns an INT. When MS SQL Server performs an operation on INT values, it returns an INT value. This means the division of two INT values will always return an INT.

The solution is to cast at least one of the two values to a FLOAT. The reason my first query was working fine is because the Rating column was a FLOAT column, which meant the division would return a FLOAT value.  The following is the same query, but will correctly return a FLOAT.

select
    cast(count(1) as float) / (select count(1) from Sales where datediff(dd, OrderDate , getutcdate()) = 0 ) as SameDayShipPct
from
    Sales
where
    datediff(dd, OrderDate , getutcdate()) = 0
and
    datediff(dd, ShipDate , getutcdate()) = 0

Take a look at the following in Query Analyzer to see how MS SQL Server handles the division operation:

declare @r1 float, @r2 float, @r3 float
set @r1 = 3/4
set @r2 = 3/4.0
set @r3 = 3/cast(4 as float)
print @r1
print @r2
print @r3

Just something to keep in mind when using division operations in SQL Server. I thought I could simple cast my expression to a FLOAT to fix the problem, but since the division operation has already converted the value to an INT the resulting value will still be based on the original INT value.

NOTE:
Always remember to watch out for potential "Divide by zero error encountered" errors when performing division operations in SQL server. This can easily crop up, so make sure your query accounts for the possibility. An easy workaround is do something like:

ISNULL(dividend / NULLIF(expression, 0), 0)

The NULLIF(expression, 0) will make the value return null if the expression ends up being 0. This will then make the division operation return null as the result. The ISNULL() will then convert the resulting null value so that it actually returns 0 instead of null.
Categories: SQL

10 Comments

  • John Gag's Gravatar
    John Gag
    I had a similar problem. If you multiply one or all of the variables by 1.00 then MS SQL will return non integer values. If all the values are integers it will round to the nearest integer. Check out this blog post -
    http://www.cftips.net/post.cfm/sql-server-rounding... and the first comment
  • Michael Sharman's Gravatar
    Michael Sharman
    >ISNULL(dividend / NULLIF(expression, 0), 0)

    Nice :)
  • Dan G. Switzer, II's Gravatar
    Dan G. Switzer, II
    @John:

    It's not that it rounds, it's that MSSQL determines the datatype based upon the types in the equation. If you divide two INTs, SQL determines that it should return an INT. If your using multiple datatypes, it will try to decide which datatype is the most appropriate for the return value. That's why converting at least one of the values to a FLOAT makes the operation return the expected value.

    @Michael:

    Thanks, I thought it was a slick solution too. :) You can also leave the ISNULL() portion off if you want it to return NULL--which would allow you to know that the divisor was zero.
  • Ali Husain's Gravatar
    Ali Husain
    Thank You! Thank You! Thank You!!!
    I had determined a sum of some numbers and then I was doing a divide.
    Each number had a valid value but when I divided them I always returned 0.
  • Dan Roycroft's Gravatar
    Dan Roycroft
    Thank you SOOO Much. This was killing me - I'm looking at my code and just knew it was something like this that I had missed. Cast works perfectly.
  • Damon Kirk's Gravatar
    Damon Kirk
    Great solution, thank you. Can someone post the ISNULL example above in an actual query? I'm having trouble getting the syntax correct.
  • Dan G. Switzer, II's Gravatar
    Dan G. Switzer, II
    @Damon:

    Here's an example:

    declare @i float
    set @i = 0

    select
        1/nullif(@i, 0) as null_if_dividend_is_zero
        , isnull(1/nullif(@i, 0), 0) as zero_if_dividend_is_zero

    Change @i to 4 to see how the query changes if the value isn't zero.
  • Damon Kirk's Gravatar
    Damon Kirk
    Perfect, thanks @Dan, worked like a charm! I really appreciate it. Hopefully that helps others as well.
  • angela's Gravatar
    angela
    thank you so much!!! i'm so happy i found this blog! i never would've figured that out.
  • Mak's Gravatar
    Mak
    Thanks for posting this. I used CONVERT(float, COUNT(*))

Comments for this entry have been disabled.