T-SQL average rounded to the closest integer

Select cast(AVG(columnname) as integer)

If you are in SQL Server, just use round(avg(column * 1.0), 0).

The reason for * 1.0 is because sql server in some cases returns calculations using the same datatype of the values used in the calculation. So, if you calculate the average of 3, 4 and 4, the result is 3.66..., but the datatype of the result is integer, therefore the sql server will truncate 3.66... to 3, using * 1.0 implicit convert the input to a decimal.

Alternatively, you can convert or cast the values before the average calculation, like cast(column as decimal) instead of using the * 1.0 trick.

If your column it's not a integer column, you can remove the * 1.0.

PS: the result of round(avg(column * 1.0), 0) still is a decimal, you can explicit convert it using convert(int, round(avg(column * 1.0), 0), 0) or just let whatever language you are using do the job (it's a implicit conversion)

This should do it. You might need a GROUP BY on the End depending on what you are looking for the average of.

SELECT CONVERT(int,ROUND(AVG(ColumnName),0))
FROM 
TableName

EDIT: This question is more interesting than I first thought.

If we set up a dummy table like so...

WITH CTE

AS

(
    SELECT 3 AS Rating
    UNION SELECT 4
    UNION SELECT 7
)


SELECT AVG(Rating)
FROM 
CTE

We get an integer average of 4

However if we do this

WITH CTE

AS

(
    SELECT 3.0 AS Rating
    UNION SELECT 4.0
    UNION SELECT 7.0
)


SELECT AVG(Rating)
FROM 
CTE

We get a decimal average of 4.666..etc

So it looks like the way to go is

WITH CTE

AS

(
    SELECT 3 AS Rating
    UNION SELECT 4
    UNION SELECT 7
)
SELECT CONVERT(int,ROUND(AVG(CONVERT(decimal,Rating)),0))
FROM CTE

Which will return an integer value of 5 which is what you are looking for.

This worked for it:

CONVERT(int,ROUND(AVG(CAST(COLUMN-NAME AS DECIMAL)) ,0))

Isn't there a shorter way of doing it though?