I am trying to calculate the percentage of all responses that are 'good' or 'very good' over the total received by date and team -. Is there a way to put a calculation within the case statement - all my attempts are not getting me anywhere? Or do I need to take a completely different approach?

SQL management studio working code so far:

SELECT 
  [Team]
 ,[response]
  ,[count]
  ,[date]
  CASE 
  WHEN  [Response] = 'Good' OR [response ] = 'Very good' THEN [count]  Else 0 end AS [positive]
  ,CASE
 WHEN [response] = 'poor' OR [response]  = 'very poor' then [count]  ElSE 0 
 END AS [negative]
 FROM [surveyresponse] Group by[Team], [response],[count],[date]

Data table with two additional fields :

Desired

CodePudding user response：

You can solve this problem with two conditional sums, whereas:

• the former will sum up all good responses
• the latter will sum up all responses which are either good or poor

SELECT Team,
       Date_,
       SUM(CASE WHEN Response LIKE '%Good' THEN Count_ END)
         / SUM(CASE WHEN Response <> 'Dont Know' THEN Count_ END) *100 AS perc 
FROM tab
GROUP BY Team,
         Date_

This will work with all the most common DBMS', unless you have some kind of legacy version that misses some of the employed operators here.

Note: Tend to avoid field names like "Date" and "Count", as they can be DMBS reserved words, and using them may lead to potential query errors.

CodePudding user response：

;WITH temp AS (
SELECT 
  [Team]
 ,[response]
 ,[count]
 ,[date_]
 ,SUM([count]) OVER ( PARTITION BY [Team]) AS Total
 ,SUM(CASE WHEN  [Response] LIKE '%good' THEN [count]  Else 0 END) OVER ( PARTITION BY [Team]) AS [Result]
 FROM [surveyresponse]
)
SELECT 
  [Team]
 ,[date_]
 ,[Result] / Total * 100 AS Percentage
FROM temp

This should work.