Here is a small cautionary tale for those, who use Average function in SQL Server. The data type of the aggregated data element greatly influences the result of AVG aggregate function.
With source data set defined as INT, the result of the straight AVG function will produce 5. However, if we were to convert numbers to float, the result of the aggregation will become 5.5. If we than round up that number, than the result of the aggregation now becomes 6.
I guess the best practice would be to always make sure you convert source data to float before applying AVG aggregation function.
;WITH CTE AS
(
SELECT 3 AS Rating
UNION SELECT 4
UNION SELECT 7
UNION SELECT 8
)
SELECT
AVG(Rating) as average_of_int,
AVG(cast(Rating as float)) as average_of_float,
round(AVG(cast(Rating as float)),0) as average_of_float_rounded
FROM
CTE
With source data set defined as INT, the result of the straight AVG function will produce 5. However, if we were to convert numbers to float, the result of the aggregation will become 5.5. If we than round up that number, than the result of the aggregation now becomes 6.
I guess the best practice would be to always make sure you convert source data to float before applying AVG aggregation function.
;WITH CTE AS
(
SELECT 3 AS Rating
UNION SELECT 4
UNION SELECT 7
UNION SELECT 8
)
SELECT
AVG(Rating) as average_of_int,
AVG(cast(Rating as float)) as average_of_float,
round(AVG(cast(Rating as float)),0) as average_of_float_rounded
FROM
CTE
Result:
average_of_int average_of_float average_of_float_rounded
————– ———————- ————————
5 5.5 6