SQL Server: rounded decimal and conversion to int (inside Select)

Question

I use the following line inside Select, which returns a decimal number, for example 33.33333.

How can I get around this inside Select and convert to integers so that I don't have decimals, for example. in the above example should it return 33?

100 * AVG(CASE WHEN col2 = col3 THEN 1.0 ELSE 0.0 END) AS matchPercent 

Thanks so much for any help with this, Tim.

Answer 1

You can use the ROUND function to round a value to an integer:

 ROUND(INT, 100 * AVG(CASE WHEN col2 = col3 THEN 1.0 ELSE 0.0 END), 0) AS matchPercent 

This will save the type, for example, a rounded float will remain a float . If you also need to return an int data type (or other integer data types), you also need to convert it:

 CONVERT(INT, ROUND(INT, 100 * AVG(CASE WHEN col2 = col3 THEN 1.0 ELSE 0.0 END), 0)) AS matchPercent 

Answer 2

Use the round function to round a number:

 ROUND(100 * AVG(CASE WHEN col2 = col3 THEN 1.0 ELSE 0.0 END), 0) AS matchPercent 

Answer 3

If you want to round a number first and then convert it to an integer, you can also add 0.5 to the number you want to convert to an integer.