SQL 2017 rounds of decimal number using "FLOOR" function

Question

I have been calculating different integer percentages with different numbers but each time I get floor rounded number. select 13*100/60 gives me 21 and the actual number is 21.66 which using a round function should give us 22 but it can only give me 21 for all different decimal numbers.

I am using SQL 2017. please help

This same problem was asked about only 20 minutes ago: [Why is the Data in my Computed Columns showing up with null / 0 values](https://stackoverflow.com/q/58502938/3484879) — Thom A, Oct 22 '19 at 11:36

score 0 · Answer 1 · answered Oct 22 '19 at 11:32

This is due to the fact that you are dividing ints and not floating-point numbers. Integer division returns an integer.

Try the following instead (noting the .0 on the end of the 60):

SELECT 13 * 100 / 60.0

Making one of the components a floating-point number will automatically output the result as a floating-point number.

Output:

21.666666

Incidentally, if you are working with variables and one of them is a FLOAT, it will automatically produce the output you expect:

DECLARE @A FLOAT
DECLARE @B INT
DECLARE @C INT

SET @A = 13
SET @B = 100
SET @C = 60

SELECT @A * @B / @C

Output:

21.6666666666667

SQL 2017 rounds of decimal number using "FLOOR" function

1 Answers1