title: Operand data type varchar is invalid for avg operator
body: <p>I keep getting the above error message </p>
  
  <blockquote>
    <p>"Operand data type varchar is invalid for avg operator" </p>
  </blockquote>
  
  <p>Can anyone fix it for me? PLEASE</p>
  
  <pre><code>WITH Average -- Calculating Mean
  AS (
      SELECT avg(convert(VARCHAR(5), DateDiff(s, [ARRIVAL_DATE_TIME], [COMPLETE_DATE_TIME]) / 3600) + ':' + convert(VARCHAR(5), DateDiff(s, [ARRIVAL_DATE_TIME], [COMPLETE_DATE_TIME]) % 3600 / 60) + ':' + convert(VARCHAR(5), (DateDiff(s, [ARRIVAL_DATE_TIME], [COMPLETE_DATE_TIME]) % 60))) AS Average
      FROM [CLERKS]
      WHERE [ARRIVAL_DATE_TIME] &gt;= DATEADD(dd, - 30, getdate() - 1)
      )
      ,data
  AS (
      SELECT cast(ARRIVAL_DATE_TIME AS DATE) AS Attendance_Date
          ,avg(convert(VARCHAR(5), DateDiff(s, [ARRIVAL_DATE_TIME], [COMPLETE_DATE_TIME]) / 3600) + ':' + convert(VARCHAR(5), DateDiff(s, [ARRIVAL_DATE_TIME], [COMPLETE_DATE_TIME]) % 3600 / 60) + ':' + convert(VARCHAR(5), (DateDiff(s, [ARRIVAL_DATE_TIME], [COMPLETE_DATE_TIME]) % 60))) AS Arr_Com
      FROM [Clerks]
      WHERE [ARRIVAL_DATE_TIME] &gt;= DATEADD(dd, - 30, getdate() - 1)
      GROUP BY cast(ARRIVAL_DATE_TIME AS DATE)
      )
  SELECT a.Attendance_Date
      ,a.Arr_Com
      ,c.Average
      ,abs(a.Arr_Com - b.Arr_Com) AS MR
  FROM data a
  LEFT JOIN data b ON cast(a.Attendance_Date AS DATETIME) = cast(b.Attendance_Date AS DATETIME) + 1
  CROSS JOIN Average c
  ORDER BY a.Attendance_Date
  GO
  </code></pre>
  
  <p>Thanks in Advance</p>