求助：SQL聚合子查询需返回与主查询分组匹配的Agent通话统计值

数据老陈

2026-8-18

按Agent分组统计特定条件通话数的SQL修正

你的问题出在子查询没有关联外部分组的Agent，导致子查询统计的是全局符合条件的通话总数，而非每个Agent的对应数据。只需给子查询的表更换别名避免冲突，并添加与外部Agent的关联条件即可解决。

修改后的完整查询：

SELECT c.Contact as [Agent]
,COUNT(df.DynamicFileID) as [Total Number Calls] ---Total Number Calls
,(SELECT COUNT(df_sub.DynamicFileID) --- Calls with Right Party Contact
    FROM DynamicFile df_sub
    LEFT JOIN DynamicFileExtension_79 dfe_sub ON dfe_sub.DynamicFileID = df_sub.DynamicFileID
    LEFT JOIN DynamicFileExtension_79_1 dfe1_sub ON dfe1_sub.DynamicFileID = df_sub.DynamicFileID
    LEFT JOIN Contact c_sub ON c_sub.ExternalSourceID = dfe_sub.Custom_CallAgent
    WHERE df_sub.FiletemplateID = @CallHistoryFTID
        AND df_sub.Status = 'Complete'
        AND dfe1_sub.Custom_WasBorrowerSpokenWith IN ('Authorized Contact','Spoke with Borrower')
        AND (dfe_sub.Custom_CallResult = 'Promise to Pay'
            OR dfe1_sub.Custom_IsPropertyOwnerOccupied = 'Yes' AND dfe1_sub.Custom_IsPropertyInGoodCondition = 'Yes')
        AND c_sub.Contact = c.Contact -- 关联当前分组的Agent
) as [Calls with Right Party Contact]
FROM DynamicFile df
LEFT JOIN DynamicFileExtension_79 dfe ON dfe.DynamicFileID = df.DynamicFileID
LEFT JOIN DynamicFileExtension_79_1 dfe1 ON dfe1.DynamicFileID = df.DynamicFileID
LEFT JOIN Contact c ON c.ExternalSourceID = dfe.Custom_CallAgent
WHERE df.FiletemplateID = @CallHistoryFTID
GROUP BY c.Contact

关键修改点：

子查询中所有表使用独立别名（如df_sub、dfe_sub），避免与外部查询的表别名冲突，防止逻辑歧义。
在子查询的WHERE末尾添加c_sub.Contact = c.Contact，强制子查询只统计当前分组Agent的符合条件通话。如果担心Contact字段可能重复，也可以用更精准的关联条件：
```
AND dfe_sub.Custom_CallAgent = dfe.Custom_CallAgent
```
这基于Agent关联的原始字段ExternalSourceID与Custom_CallAgent的对应关系，可靠性更高。