How to find percentage of sum with multiple like conditions

Question

SELECT DISTINCT CASE WHEN IncidentTypeCode like ('[][5-9][0-9]') OR (IncidentTypeCode like ('[5-9][0-9][a-z][0-9][0-9]') OR IncidentTypeCode like ('[5-9][0-9][a-z][0-9][0-9][a-z]'))THEN 'Fire' WHEN IncidentTypeCode like ('[][0-3][0-9]') OR (IncidentTypeCode like ('[0-3][0-9][a-z][0-9][0-9]') OR IncidentTypeCode like ('[0-3][0-9][a-z][0-9][0-9][a-z]') OR IncidentTypeCode in ('_NT','_ET','_ma')) THEN 'Medical' END AS callType ,IncidentNumber ,IncidentTypeCode ,IncidentDate FROM MV_Incident WHERE IncidentDate >='11/6/2020'--@IncidentDate --AND IncidentDate <= @IncidentDate2 --AND ReportNumberAgencyID IN (@Agency) AND FirstUnitDispatchedTime IS NOT NULL AND IncidentTypeCode NOT IN ('_test','_SD','BP') ORDER BY IncidentDate I'm trying to calculate what percentage of incident numbers were 'medical' and what percentage were 'fire'. Unfortunately I do not have a specific column that categorizes the call types this way except for a case statement. I can't seem to find the right syntax to use because I have several conditions. When I run the query above I get a result like this: ![38132-results.jpg][1] [1]: /api/attachments/38132-results.jpg?platform=QnA EDIT 11/20/2020 After further investigation I realized that the query below in the answer was not equaling 100% and it was giving different calculations than when the math was preformed manually. I collaborated with another resource within our organization and discovered that the following query met the need requested above. WITH CTE AS ( SELECT COUNT (DISTINCT CASE WHEN IncidentTypeCode like '[][0-3][0-9]' OR (IncidentTypeCode like '[0-3][0-9][a-z][0-9][0-9]' or (IncidentTypeCode like '[0-3][0-9][a-z][0-9][0-9][a-z]' or (IncidentTypeCode in ('_NT','_ET','ma')))) then IncidentNumber END) as MedicalCount ,COUNT (DISTINCT CASE WHEN IncidentTypeCode like '[][5-9][0-9]' OR (IncidentTypeCode like '[5-9][0-9][a-z][0-9][0-9]' or (IncidentTypeCode like '[5-9][0-9][a-z][0-9][0-9][a-z]')) then IncidentNumber END) as FireCount ,COUNT(DISTINCT CASE WHEN IncidentTypeCode NOT IN ('_test','_SD','_BP') then IncidentNumber END) AS CountofAllCalls FROM MV_Incident WHERE IncidentDate >=@IncidentDate AND IncidentDate <= @IncidentDate2 AND ReportNumberAgencyID IN (@Agency) AND FirstUnitDispatchedTime IS NOT NULL AND IncidentTypeCode NOT IN ('_test','_SD','_BP') ) SELECT * ,sum(medicalcount) * 100.00/ sum(sum(countofallcalls)) over () as medpercnt ,sum(FireCount) * 100.00/ sum(sum(countofallcalls)) over () as firepercnt FROM CTE group by medicalcount, firecount,CountofAllCalls

Answer 1

Hi anonymous user,

Please also refer below query and check whether it is helpful to you:

; WITH CTE AS (  
      SELECT   
         CASE  
          WHEN IncidentTypeCode like ('[_][5-9][0-9]') OR (IncidentTypeCode like ('[5-9][0-9][a-z][0-9][0-9]') OR IncidentTypeCode like ('[5-9][0-9][a-z][0-9][0-9][a-z]'))THEN  'Fire'   
          WHEN IncidentTypeCode like ('[_][0-3][0-9]') OR (IncidentTypeCode like ('[0-3][0-9][a-z][0-9][0-9]') OR IncidentTypeCode like ('[0-3][0-9][a-z][0-9][0-9][a-z]') OR IncidentTypeCode in ('_NT','_ET','_ma'))  THEN 'Medical'      
          END AS callType    
        ,IncidentNumber  
        ,IncidentTypeCode  
        ,IncidentDate    
      FROM  MV_Incident   
      WHERE  
        IncidentDate >='11/6/2020'--@IncidentDate  
        --AND IncidentDate <= @IncidentDate2  
        --AND ReportNumberAgencyID IN (@Agency)  
        AND FirstUnitDispatchedTime IS NOT NULL  
        AND IncidentTypeCode NOT IN ('_test','_SD','_BP')  
 )  
  select a.*,b.percentage    
   FROM   CTE a  
  inner join   
 (SELECT callType,  
  convert(decimal(5, 2),Count(callType)* 100 / (Select Count(*) From CTE)*0.01) as percentage   
 FROM   CTE  
  group by callType) b  
  on a.callType=b.callType  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet

Answer 2

Put your query to the CTE and then you can calculate the percentages of different call types.

Answer 3

Elaborating on Guoxiong's suggestion:

; WITH CTE AS (
     SELECT 
        CASE
         WHEN IncidentTypeCode like ('[_][5-9][0-9]') OR (IncidentTypeCode like ('[5-9][0-9][a-z][0-9][0-9]') OR IncidentTypeCode like ('[5-9][0-9][a-z][0-9][0-9][a-z]'))THEN  'Fire' 
         WHEN IncidentTypeCode like ('[_][0-3][0-9]') OR (IncidentTypeCode like ('[0-3][0-9][a-z][0-9][0-9]') OR IncidentTypeCode like ('[0-3][0-9][a-z][0-9][0-9][a-z]') OR IncidentTypeCode in ('_NT','_ET','_ma'))  THEN 'Medical'    
         END AS callType  
       ,IncidentNumber
       ,IncidentTypeCode
       ,IncidentDate  
     FROM  MV_Incident 
     WHERE
       IncidentDate >='11/6/2020'--@IncidentDate
       --AND IncidentDate <= @IncidentDate2
       --AND ReportNumberAgencyID IN (@Agency)
       AND FirstUnitDispatchedTime IS NOT NULL
       AND IncidentTypeCode NOT IN ('_test','_SD','_BP')
)
SELECT callType, IncidentNumber, IncidentTypeCode, IncidentDate,
       convert(decimal(5, 2), 1E2*COUNT(*) OVER (PARTITION BY CallType) / COUNT(*))
FROM   CTE