Operand data type varchar(max) is invalid for avg operator

Question

Can someone help me with this error?

SELECT 
   a.id, a.name, a.Report, b.Agentid, 
   count(distinct b.pyID), 
   count(distinct b.SourceID), 
   AVG(b.ChatDurationMinute) 
from table_1 a 
left join table_b b on a.id = b.agentid 
where 
   StartChatTime >= ''20200701'' and 
   LastChatTime <= ''20200831''  
GROUP BY a.id, a.name, a.Report, b.AgentID

And getting error like this:

Operand data type varchar(max) is invalid for avg operator.

What should I do? Thanks to anyone who helps me.

Answer 1

Based on the naming conventions in the queries, it is very reasonable to assume that StartChatTime and LastChatTime are in b and not a. If so, then the WHERE clause is turning the LEFT JOIN into an INNER JOIN.

In addition, including b.AgentId in the GROUP BY is redundant, because the ON clause specifies that it should be the same as a.id.

Storing numbers in strings is a bad, bad design. But you should probably use one of the try_() functions if you are stuck with a bad, bad design.

Let me assume that ChatDurationMinute is an integer. Then:

SELECT a.id, a.name, a.Report
       COUNT(DISTINCT b.pyID), 
       COUNT(DISTINCT b.SourceID), 
       AVG(TRY_CONVERT(int, b.ChatDurationMinute) * 1.0)
FROM table_1 a LEFT JOIN
     table_b b 
     ON a.id = b.agentid AND
        b.StartChatTime >= '20200701'' AND 
        b.LastChatTime <= '20200831'  
GROUP BY a.id, a.name, a.Report;

If those dates are really in a, then you can keep them in the WHERE clause.