1
select concat('3-',`rcr`.`TargetID`,'-',`rcr`.`CheckCategoryID`) AS `JoinID`,`rcr`.`TargetID` AS `ClientID`,`rcr`.`CheckCategoryID` AS `CheckCategoryID`,`rcr`.`HealthCheckGUID` AS `HealthCheckGUID`,`rcr`.`CheckName` AS `CheckName`,`rcr`.`CheckOrder` AS `CheckOrder`,`rcr`.`GroupCheckOrder` AS `GroupCheckOrder`,avg(`rcr`.`TargetStraightScore`) AS `TargetStraightScore`,avg(`rcr`.`TargetWeightedScore`) AS `TargetWeightedScore`,`rcr`.`TargetGroupData` AS `TargetGroupData`,count(distinct `rcr`.`HealthCheckGUID`) AS `NumChecks`,max((case when (`sp`.`PropertyName` = 'HowToWeighScores') then `sp`.`Value` end)) AS `ScoringMethod`,max((case when (`sp`.`PropertyName` = 'GreenMin') then (`sp`.`Value` * 100) end)) AS `GreenMin`,max((case when (`sp`.`PropertyName` = 'YellowMin') then (`sp`.`Value` * 100) end)) AS `YellowMin`,max((case when (`sp`.`PropertyName` = 'YellowIsUnhealthy') then `sp`.`Value` end)) AS `YellowIsUnhealthy` from (`labtech`.`plugin_sap_reportcheckresults` `rcr` join `labtech`.`plugin_sap_properties` `sp`) where (`rcr`.`TargetType` = 3) group by `rcr`.`TargetID`,`rcr`.`CheckCategoryID`,`rcr`.`GroupCheckOrder`