Search code examples
reporting-servicessumcase

Counting Instances of Criteria in 2 separate fields

This is a bit hard to explain but this is the query:

SELECT        rqia_name AS Inspection, SUM((CASE WHEN Filteredrqia_inspection.rqia_inspectorconductingidname = 'External Professional' OR
                         Filteredrqia_inspection.rqia_inspectorconducting2name = 'External Professional' OR
                         Filteredrqia_inspection.rqia_inspectorsupporting1idname = 'External Professional' OR
                         Filteredrqia_inspection.rqia_inspectorsupporting2idname = 'External Professional' THEN 1 ELSE 0 END)) AS ExtProfInvolved
FROM            Filteredrqia_inspection
GROUP BY rqia_name
HAVING        (SUM((CASE WHEN Filteredrqia_inspection.rqia_inspectorconductingidname = 'External Professional' OR
                         Filteredrqia_inspection.rqia_inspectorconducting2name = 'External Professional' OR
                         Filteredrqia_inspection.rqia_inspectorsupporting1idname = 'External Professional' OR
                         Filteredrqia_inspection.rqia_inspectorsupporting2idname = 'External Professional' THEN 1 ELSE 0 END)) > 0)

So what I want to do is SUM the instances within each inspection where External Professional appears. I do get a SUM of 1 if the External Professional appears in any one of the above fields, but there are instances where it may appear in both 'Filteredrqia_inspection.rqia_inspectorsupporting1idname' AND 'Filteredrqia_inspection.rqia_inspectorsupporting2idname', in which case I want to show the SUM CASE as 2. However, even if the External Professional shows in more than one field for an inspection, the SUM CASE statement still returns a 1. How can i get this to do a proper count? Thanks in advance.

As above, but didn't work.

Solution

  • I think you can add a WHEN clause as the first WHEN in your CASE that checks for the two External Pros and returns 2 if so to make it work the way you need.

    CASE 
    WHEN Filteredrqia_inspection.rqia_inspectorsupporting1idname = 'External Professional' AND Filteredrqia_inspection.rqia_inspectorsupporting2idname = 'External Professional' 
        THEN 2
    WHEN...

    This way it will check for the two matches first before doing the current check. The CASE returns the first match of the WHEN statements so dual Pro would match on the first WHEN and ignore the second WHEN.