CODEWITHSUNDEEP
sqlsql-server
Fischer Ludrian
Fischer Ludrian

Reputation: 677

CAST is not working in SQL query

I use the StackExchange Data Explorer to calculate the average number of answers for Java questions that are not closed and have an accepted answer:

DECLARE @tagId int;

SELECT  @tagId = Id
FROM    Tags         
WHERE   TagName = 'java';

SELECT  CAST(AVG(AnswerCount) as DECIMAL(5,2)) 
FROM    posts p 
JOIN    PostTags pt ON pt.PostId = p.Id
WHERE   pt.TagId = @tagId
    AND     p.PostTypeID = 1
    AND     p.AcceptedAnswerID IS NOT NULL
    AND     p.ClosedDate IS NULL;

However, I always get 2 as a result, even so I try to cast the result to a decimal. I also tried to cast first each result and then cast the average again, but still, I get 2 as a result instead 2.29....

What's my error?

Upvotes: 1

Views: 6284

Answers (2)

Mureinik
Mureinik

Reputation: 312146

avg returns the same data type as it receives. So calculating avg on ints will return an int (in your case, 2). Any widening cast done afterwards is meaningless, as the precision is already lost. One way around this is to cast your column to decimal before feeding it to the avg function:

SELECT AVG(CAST(AnswerCount) as DECIMAL(5,2)) 
FROM posts p 
JOIN PostTags pt ON pt.PostId = p.Id
WHERE pt.TagId = @tagId
AND p.PostTypeID = 1
AND p.AcceptedAnswerID IS NOT NULL
AND p.ClosedDate IS NULL;

Upvotes: 1

Raj More
Raj More

Reputation: 48034

Try moving the CAST inside the AVG statement like this:

SELECT AVG(CAST(AnswerCount as DECIMAL(5,2))) 
FROM posts p 
join PostTags pt 
     on pt.PostId = p.Id
WHERE pt.TagId = @tagId
AND p.PostTypeID = 1
AND p.AcceptedAnswerID IS NOT NULL
AND p.ClosedDate IS NULL;

Upvotes: 4

Related Questions