T-SQL: Calculating with Integers

T-SQL is Transact SQL which is the version of SQL used with Microsoft SQL Server

This brief note explains a problem you may encounter when doing calculations with integers - and how to solve it

Take two integers and divide one by the other, then multiply by 100 to express as a percentage

Say 949 divided by 1001 multiplied by 100

The result expressed to two decimal places is 94.81  (%)

Now try this in T-SQL as follows:

DECLARE @Percentage as DECIMAL(5,2)
DECLARE @intNumerator as INT =949
DECLARE @intDemoninator  as INT = 1001

SET @Percentage=100* (@intNumerator/@intDemoninator)
SELECT  @Percentage

The result will be 0.00

So what is going wrong?

The 'problem' is with the integers. The result of a calculation using two integers will itself be an integer. It rounds the result down to the nearest whole number - in this case 0.9481 rounds down to zero

SELECT 949/1001  gives 0

And 100 * 0 is still 0

Had we swapped the numerator and denominator around the result, being greater than 1 would have rounded to 1

SELECT 1001/949 gives 1


What is the solution?

The solution is to convert the integers to the float data type before attempting the calculation
Everything works smoothly then

DECLARE @Percentage as DECIMAL(5,2)
DECLARE @intNumerator as INT =949
DECLARE @intDemoninator  as INT = 1001

SET @Percentage=100* (cast(@intNumerator as float)/cast (@intDemoninator as float))
SELECT  @Percentage

gives  94.81


If you read internet problem solving sites, the cognoscenti like to tell the novices that this is 'known behaviour' implying, of course, that they would never ever have made this mistake

Yea!