At first glance, calculating an average in SQL Server seems straightforward. Just wrap a column in the AVG() function and you’re done. But there’s a subtle catch when working with integer columns. If you pass an integer to AVG() the result will be an integer, even if the actual average includes a fractional part. If you’re not aware of this when calculating averages, you could potentially draw the wrong conclusion from your query results.
Let’s unpack the behavior and then see how we can fix it.
How SQL Server Determines the Return Type of AVG()
The return type of AVG() depends on the data type of the input column. Here’s the breakdown according to the official documentation:
| Input Type | Return Type |
|---|---|
tinyint, smallint, int | int |
bigint | bigint |
decimal category (p, s) | decimal(38, max(s,6)) |
money / smallmoney | money |
float / real | float |
The important thing to remember is that if your column is any of the integer types, the average is also returned as an integer. In other words, fractional parts get dropped from the return value.
Example Without CAST()
Suppose we have a table of product ratings stored as integers:
CREATE TABLE ProductRatings (
ProductID INT,
Rating INT
);
INSERT INTO ProductRatings (ProductID, Rating) VALUES
(1, 4),
(2, 5),
(3, 3),
(4, 2);Now let’s calculate the average:
SELECT AVG(Rating) AS AvgRating
FROM ProductRatings;Output:
AvgRating
---------
3
How this is calculated:
- Values: 4, 5, 3, 2
- Total = 14
- Count = 4
- True average = 14 ÷ 4 = 3.5
- Return type =
int - Return value = 3
The decimal part is silently dropped because the return type is int.
Example With CAST()
If we want the fractional part, we’ll need to cast the column before averaging:
SELECT AVG(CAST(Rating AS DECIMAL(5,2))) AS AvgRating
FROM ProductRatings;Result:
AvgRating
---------
3.5
Now:
- Total = 14
- Count = 4
- Average = 14 ÷ 4 = 3.50
- Return type =
decimal(5,2) - Return value = 3.50
This time we get the correct value.
Why This Matters
If you’re reporting customer ratings, delivery times, or any metric where fractions are important, relying on the default AVG() for integer columns can understate the result.
For example, saying “the average rating is 3” instead of “3.5” is misleading. Casting ensures you don’t lose precision.
Takeaway
AVG()returns an integer result if the input is any integer type (tinyint,smallint,int,bigint).- To get a precise average with decimals, cast the input to
DECIMAL,NUMERIC, orFLOAT.