带有Where和Count函数的SQL Case语句

Question

无法运行此查询。我想得到网站的发起人和降级者的数量。

SELECT 
    Site, COUNT(*) AS Count,
    CASE WHEN NPS <= 6 THEN 1 ELSE 0 END AS demoter,
    CASE WHEN NPS >= 9 THEN 1 ELSE 0 END AS promoter
FROM 
    [D998AA0_CXS].[APD_CXS].[SURVEY_DETAILS]
WHERE 
    [CaseNumber] NOT LIKE '%C%' 
    AND CONVERT(date, DateCompleted) BETWEEN '2022-08-29'  AND '2022-09-02'
    AND Region = 'United States' 
    AND Channel = 'Phone' 
    AND SkillCategory = 'Customer Service'
    AND SkillName IN ('Tracking', 'Universal', 'Shipping')
GROUP BY 
    Site

​

​

我尝试使用count函数运行，但得到了一个错误：

Msg 8120，16级，状态1，第2行

列'D998AA0_CXS.APD_CXS.SURVEY_DETAILS.NPS‘在select列表中无效，因为它既不包含在聚合函数中，也不包含在GROUP子句中

Answer 1

问题中没有足够的信息来给出一个好的答案；我们所能做的只是猜测。以下是两种猜测：

第一：

SELECT Site, COUNT(*) as Count,
    CASE WHEN NPS <= 6 THEN 1 ELSE 0 END AS demoter,
    CASE WHEN NPS >= 9 THEN 1 ELSE 0 END AS promoter
FROM [D998AA0_CXS].[APD_CXS].[SURVEY_DETAILS]
WHERE [CaseNumber] NOT LIKE '%C%' 
    AND CONVERT(date,DateCompleted) BETWEEN '2022-08-29'  AND '2022-09-02'
    AND Region = 'United States' and Channel = 'Phone' and SkillCategory = 'Customer Service'
    AND SkillName In ('Tracking','Universal','Shipping')
GROUP BY Site, NPS

第二：

SELECT Site, COUNT(*) as Count,
    CASE WHEN SUM(NPS) <= 6 THEN 1 ELSE 0 END AS demoter,
    CASE WHEN SUM(NPS) >= 9 THEN 1 ELSE 0 END AS promoter
FROM [D998AA0_CXS].[APD_CXS].[SURVEY_DETAILS]
WHERE [CaseNumber] NOT LIKE '%C%' 
    AND CONVERT(date,DateCompleted) BETWEEN '2022-08-29'  AND '2022-09-02'
    AND Region = 'United States' and Channel = 'Phone' and SkillCategory = 'Customer Service'
    AND SkillName In ('Tracking','Universal','Shipping')
GROUP BY Site

我希望这些中的任何一个都不是正确的，但它们至少应该运行，并且根据您看到的结果以及它们与原来的结果有多大的不同，它们可以给您足够的推动力来自己完成任务。