SQL Server: round decimal number and convert to int (within Select)
41,130
Solution 1
You can use ROUND
function to round the value to integer:
ROUND(INT, 100 * AVG(CASE WHEN col2 = col3 THEN 1.0 ELSE 0.0 END), 0) AS matchPercent
This will retain the type, e.g rounded float
will stay float
. If you also need to return int
data type (or other integer data types), you need to also convert it:
CONVERT(INT, ROUND(INT, 100 * AVG(CASE WHEN col2 = col3 THEN 1.0 ELSE 0.0 END), 0)) AS matchPercent
Solution 2
Use the round
function to round the number:
ROUND(100 * AVG(CASE WHEN col2 = col3 THEN 1.0 ELSE 0.0 END), 0) AS matchPercent
Solution 3
If you want to round the number first and then convert it to an integer, you also can add 0.5 to the number that you want to convert to an integer.
Author by
user2571510
Updated on July 05, 2022Comments
-
user2571510 almost 2 years
I am using the following line within a Select which returns a number with decimals, e.g. 33.33333.
How can I round this within the Select and convert to integers so that I don't have decimals, e.g. in the above example it should return 33 ?
100 * AVG(CASE WHEN col2 = col3 THEN 1.0 ELSE 0.0 END) AS matchPercent
-
Guffa about 10 yearsConverting to
int
won't round the number, it will truncate it. -
Szymon about 10 years@Guffa, you're right, how did I miss it? +1 for you.
-
John Pasquet almost 6 yearsMakes it pretty easy. Nice thought!