执行汇总的子查询计划
问题描述:
寻找一种优雅的方式来避免错误消息 无法对包含聚合或子查询的表达式执行聚合函数。执行汇总的子查询计划
SELECT AccountName
, SUM(CASE
WHEN CallFlag = 1 AND Attempts < c.nMaxAttempts
THEN 1
ELSE 0
END)CallRecords
, SUM(CASE
WHEN CRC IN(
SELECT szCRC COLLATE SQL_Latin1_General_CP1_CI_AS
FROM ContactCenter..CallDispositionMap
WHERE bBadRecord = 1)
THEN 1
ELSE 0
END)BadRecords
FROM Calls;
答
您需要使用子选择。而且您还需要使用Group BY
在查询上。试试这个,
SELECT
AccountName,
SUM(CallRecords) AS CallRecords,
SUM(BadRecords) AS BadRecords
FROM
(
SELECT AccountName
, (CASE
WHEN CallFlag = 1 AND Attempts < c.nMaxAttempts
THEN 1
ELSE 0
END)CallRecords
, (CASE
WHEN CRC IN(
SELECT szCRC COLLATE SQL_Latin1_General_CP1_CI_AS
FROM ContactCenter..CallDispositionMap
WHERE bBadRecord = 1)
THEN 1
ELSE 0
END)BadRecords
FROM Calls) AS data
GROUP BY AccountName
真棒......谢谢! – cherrywood 2015-04-02 16:42:43