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:
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:
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.
Take a look at the following in Query Analyzer to see how MS SQL Server handles the division operation:
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.