CODEWITHSUNDEEP
sqlsql-serverselectintrounding
user2571510
user2571510

Reputation: 11377

SQL Server: round decimal number and convert to int (within Select)

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

Upvotes: 6

Views: 51634

Answers (4)

rajquest
rajquest

Reputation: 711

Below statement rounds a decimal value to nearest integer

ROUND(decimalvalue,0)

ROUND(number, decimals, operation)

Example

ROUND(94.503,0)

Result: 95

Example

ROUND(94.403,0)

Result: 94

Upvotes: 0

Pim
Pim

Reputation: 29

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.

Upvotes: 2

Szymon
Szymon

Reputation: 43023

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

Upvotes: 14

Guffa
Guffa

Reputation: 700392

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

Upvotes: 5

Related Questions