Groupby Certain Columns to Generate Values-CodePudding

Based on certain key columns (i.e. Location, Sublocation, and Area), how can I groupby those columns to have standard values? In the below example, the only rule is, grouping by Location, Sublocation and Area, if the value of Excellent is seen two or more times (not a majority because there may be more rows for other locations) in column Initial Result for the grouped scenario, then the Final Result for the grouped columns should be PASS. If that is not the case, then FAIL. This is only a portion of the larger dataset where multiple location, sublocation, and area level combinations exist.

Input Example

Location Sublocation  Initial Results
School   Cafeteria    Excellent       
School   Cafeteria    Poor            
School   Cafeteria    Excellent       
School   Auditorium   Poor
School   Auditorium   Poor
School   Auditorium   Excellent

Output

Location Sublocation  Initial Results  Final Results
School   Cafeteria    Excellent        PASS
School   Cafeteria    Poor             PASS
School   Cafeteria    Excellent        PASS
School   Auditorium   Poor             FAIL
School   Auditorium   Poor             FAIL
School   Auditorium   Excellent        FAIL

I am thinking I can try to do a count of the value Excellent grouping by the above criteria. After I do a count, then I can simply say if it's 2 or more then create another column and mark it as pass or fail? I think this is requiring more steps then needed though...

Code attempt

select a.*, count(case when 'initial results' = 'Excellent' > 2 then 'PASS' else ' 'FAIL') end as 'Final Result'
from MyTable a
group by location, sublocation, area

I am getting numerous error messages and I think it's because I probably need to create a separate count query?

CodePudding user response：

My SQL is a little rusty but something like this should work

With cte as (
SELECT [Location],
       [Sublocation],
       SUM(CASE WHEN [initial results] = ‘Excellent’
           THEN 1
           ELSE 0
           END) as cnt
FROM MyTable
GROUP BY Location, Sublocation
)

SELECT a.* ,
(CASE WHEN c.cnt > 2 
      THEN ‘PASS’
      ELSE ‘FAIL’
      END) as [Final Results]

FROM cte c
    RIGHT JOIN MyTable a ON (c.Location = a.Location AND c.Sublocation = a.Sublocation)

Basically use a common table expression to sum where 'initial results' is equal to 'excellent'. Then join back to main table and use case logic to find where 'excellent' > 2