I have pasted a formula from Excel and pasted onto SQL Server Management Studio.

Once I execute it, I get an unexpected result:

SELECT (((10320238-2092189)/2092189)*100.0)/4.0 --returns 75.000000

But after adding precision to each value within query, it returns the correct result:

SELECT (((10320238.0-2092189.0)/2092189.0)*100.0)/4.0 --returns 98.31866289250000

Why the first query won't bring 98% as result? What is the inner data type of 10320238? Is it integer?

The engine is

Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64) 
    Oct 20 2015 15:36:27 
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

It's called "integer math" - when you divide two integers, any remainder is discarded. Compare:

SELECT integers = (10320238-2092189)/2092189, 
       decimals = (10320238.0-2092189.0)/2092189.0;


integers    decimals
--------    ------------
3           3.9327465157

From the division topic in Books Online:

If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.