Calculating Larger and Smaller Than Average Returns SQL SERVER Error

Question

In the Columns:

SUM(CASE WHEN cqd.queuetime < AVG(cqd.queuetime) THEN 1 ELSE 0 END) as 'Qtde < Média' e
SUM(CASE WHEN cqd.queuetime > AVG(cqd.queuetime) THEN 1 ELSE 0 END) as 'Qtde > Média',

returns the following error in SQL SERVER 2018:

Cannot perform an Aggregate Function on an Expression containing an Aggregate or a subquery.

What am I doing wrong?

Thank you!

full script:

select  csq.csqname as Cqs, CONVERT(TIME,ccd.startdatetime,10) "Time", r.resourcename AS Agente,
SUM(CASE WHEN cqd.queuetime > 20  THEN 1 ELSE 0 END) as 'Total(>20)',
MAX(cqd.queuetime) as '> Número',
MAX(ccd.connecttime) as '>Tempo',
SUM(CASE WHEN asd.eventtype = 1 THEN 1 ELSE 0 END) as 'Logados',
SUM(CASE WHEN asd.eventtype = 5 THEN 1 ELSE 0 END) as 'Em Atendimento',
--qtde < média qdt qtd duração de chamadas < média
SUM(CASE WHEN cqd.queuetime < AVG(cqd.queuetime) THEN 1 ELSE 0 END) as 'Qtde > Média',
MIN(cqd.queuetime) as 'Menor tempo de ligação',
AVG(cqd.queuetime) as 'Tempo médio de atendimento',
STDEVP(cqd.queuetime) as 'Desvio Padrão',
MAX(cqd.queuetime) as 'Maior tempo de ligação',
--qtde > média qtd duração de chamadas > média 
SUM(CASE WHEN cqd.queuetime > AVG(cqd.queuetime) THEN 1 ELSE 0 END) as 'Qtde > Média',
SUM(CASE WHEN cqd.disposition = 2 THEN 1 ELSE 0 END) as 'Atendidas',
SUM(CASE WHEN cqd.disposition = 1 THEN 1 ELSE 0 END) as 'Abandonadas'

from [resource] r

INNER JOIN teamcsqmapping on r.assignedteamid = teamcsqmapping.teamid 
INNER JOIN contactservicequeue csq on teamcsqmapping.csqid = csq.contactservicequeueid
INNER JOIN contactcalldetail ccd on r.resourceid = ccd.destinationID and ccd.destinationtype = 1
JOIN contactqueuedetail cqd on csq.contactservicequeueid = cqd.targetid and cqd.targettype = 0
INNER JOIN agentstatedetail asd on r.resourceid = asd.agentid

WHERE 
CONVERT(TIME,ccd.startdatetime,10) BETWEEN '14:00' AND '14:59'

GROUP BY csqname, CONVERT(TIME,ccd.startdatetime,10), r.resourcename
ORDER BY resourcename, 2 ASC

Answer 1

The mistake is very clear:

Cannot perform an Aggregate Function on an Expression containing an Aggregate or a subquery.

Or in free translation:

Cannot execute an aggregated function in an expression containing an aggregate or subquery.

The problem is that it is impossible to interpret its command in terms of aggregation. Apparently you want to indicate which records are off average. For this you must set the queries separately, one with the averages and the other with the records themselves.

An example would be the following:

SELECT CASE
         WHEN t.valor < x.media THEN 'Qtde > Média'
         WHEN t.valor > x.media THEN 'Qtde < Média'
         ELSE 'Qtde igual a média'
       END AS conclusao
  FROM tabela t
       INNER JOIN (SELECT AVG(t.valor) AS media,
                          t.id_grupo
                     FROM tabela t
                    GROUP BY t.id_grupo) x ON x.id_grupo = t.id_grupo