SQL Case Statements with Where and Count Function-CodePudding

Unable to run this query. I want to get count of promoter and demoters for Sites.

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

I tried running with count function but I get an error:

Msg 8120, Level 16, State 1, Line 2
Column 'D998AA0_CXS.APD_CXS.SURVEY_DETAILS.NPS' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

CodePudding user response：

Use conditional aggregation by moving the CASE expressions inside and aggregate function like COUNT():

SELECT Site, 
       COUNT(*) as Count,
       COUNT(CASE WHEN NPS <= 6 THEN 1 END) AS demoter,
       COUNT(CASE WHEN NPS >= 9 THEN 1 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;

In this case the CASE expressions should not have an ELSE 0 part.

CodePudding user response：

There's not enough information in the question to provide a good answer; all we can do is guess. So here are two guessess:

First:

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

Second:

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

I expect neither of these are right, but they should at least run, and based on the results you see and how they differ from the original they could give you enough of a push to finish on your own.