英文:
Getting error: "Cannot use aggregate functions inside of aggregate functions". Need help in SQL query
问题
以下是翻译好的部分：
- 为了我的需求，我创建了一个视图来进行所有的数据操作。
- 对于一天中工作在团队中的代理商，我们想要找到平均通话时间和最长等待时间。
- 我编写了以下的SQL查询：
SELECT
日期, 代理ID, 团队ID,
AVG([通话时间]) AS 平均通话时间,
MAX([等待时间]) AS 最长等待时间
FROM
tbl_contacts
GROUP BY
日期, 代理ID, 团队ID
-
现在，我有一个额外的需求，需要添加两列：
- 当等待时间达到最大值时的
通话开始时间 - 当等待时间达到最大值时的
通话结束时间
基本上，每当一天中发生最大等待时间时，我们想要查看通话何时开始以及通话何时结束。
- 当等待时间达到最大值时的
-
我尝试扩展我的查询如下：
SELECT
日期, 代理ID, 团队ID,
AVG([通话时间]) AS 平均通话时间,
MAX([等待时间]) AS 最长等待时间,
MAX(CASE
WHEN [等待时间] = MAX([等待时间])
THEN [通话开始]
END) AS [等待时间通话开始],
MAX(CASE
WHEN [等待时间] = MAX([等待时间])
THEN [通话结束]
END) AS [等待时间通话结束]
FROM
tbl_contacts
GROUP BY
日期, 代理ID, 团队ID
-
但是我遇到了这个错误：
不能在聚合函数内部使用聚合函数。
-
我需要你帮助我解决这个SQL查询问题。
-
我只需要从我的查询中返回这两列。
这就是翻译好的部分，没有包含问题或其他内容。
英文:
There is a table inside SQL Server called tbl_contacts which will look similar to this screenshot (I have just highlighted few rows in yellow because those rows are going to get aggregated so that you can see difference between input and expected output):
For my requirement, I created a view for all the data manipulations.
For an agent working in a team in a day, we wanted to find the avg talk time and max wait time.
I have written the following SQL query:
SELECT
Date, agentID, teamId,
AVG([talk time]) AS avg_talk_time,
MAX([wait time]) AS max_wait_time
FROM
tbl_contacts
GROUP BY
Date, agentID, teamId
Now, I have an additional requirement to add 2 columns,
- The
CallStarttime whenever the wait time was maximum - The
CallEndtime whenever the wait time was maximum.
Basically, whenever max wait time occurred in a day, we want to see when the call started and when the call ended.
I tried to extend my query like this:
SELECT
Date, agentID, teamId,
AVG([talk time]) AS avg_talk_time,
MAX([wait time]) AS max_wait_time,
MAX(CASE
WHEN [wait time] = MAX([wait time])
THEN [Call Start]
END) AS [Wait Time Call Start],
MAX(CASE
WHEN [wait time] = MAX([wait time])
THEN [Call End]
END) AS [Wait Time Call End]
FROM
tbl_contacts
GROUP BY
Date, agentID, teamId
But I'm getting this error:
> Cannot use aggregate functions inside of aggregate functions.
I need your help with this SQL query.
I just need to return these 2 columns from my query.
I'm also attaching the screenshot of the expected output below so that it is clear:
答案1
得分: 0
错误消息表明在聚合函数内部使用聚合函数是不允许的。一种解决方法是将您的初始查询作为子查询使用，并将其与表连接以获取相关的呼叫开始和呼叫结束值。
select s.*,
[呼叫开始] as [等待时间呼叫开始],
[呼叫结束] as [等待时间呼叫结束]
from tbl_contacts
inner join (
Select Date, agentID, teamId,
AVG([通话时间]) as 平均通话时间,
MAX([等待时间]) as 最大等待时间
from tbl_contacts
group by Date, AgentID, TeamID
) as s on s.Date = s.Date and s.agentID = t.agentID and s.teamId = t.teamId and s.max_wait_time = [等待时间]
英文:
The error message indicates that using aggregate functions within aggregate functions is not permitted. One solution is to use your initial query as a subquery and join it to the table to obtain the related call start and call end values.
select s.*,
[Call Start] as [Wait Time Call Start],
[Call End] as [Wait Time Call End]
from tbl_contacts
inner join (
Select Date, agentID, teamId,
AVG([talk time]) as avg_talk_time,
MAX([wait time]) as max_wait_time
from tbl_contacts
group by Date, AgentID, TeamID
) as s on s.Date = s.Date and s.agentID = t.agentID and s.teamId = t.teamId and s.max_wait_time = [wait time]
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论