When Calculating quantity greater and less than Average returns error in SQL SERVER

Question

When Calculating quantity greater and less than Average returns error in SQL SERVER

Navigation

#1 by (2 votes)

0

In 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:

Can not 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

sql sql-server sum

asked by anonymous 30.11.2018 / 14:05

1 answer

How to pass more than one url to ajax consume json? How to access an object within an object, and its property name is a number?

score 2 · Answer 1

The error is quite clear:

Can not perform an aggregate function on an expression containing an aggregate or a subquery.

Or in free translation:

You can not execute an aggregate function on an expression containing an aggregate or subquery.

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

An example would be as follows:

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