SELECT TblTier1.Entry AS Tier1Field, TblTier1.RecordID AS Tier1IDField,
MAX(TRIM(CONCAT(IFNULL(TRIM(TblTier2.Name), ''), ' ', IFNULL(TRIM(TblTier2.SubName), ''), ': ', IFNULL(TRIM(TblTier2.City), ''), ', ', IFNULL(TRIM(TblTier2.State), '')))) AS Tier2Field,
TblTier2.RecordID AS Tier2IDField, TRIM(CONCAT(TRIM(TblTier3.Name), ' ', IFNULL(TRIM(TblTier3.SubName), ''))) AS Tier3Field,
TblTier3.RecordID AS Tier3IDField,
surveys.RecordID AS SurveyID, IFNULL(CONCAT('Survey Started: ', DATE_FORMAT(surveys.DateStarted, '%m/%d/%Y')), 'No Survey Date') AS DateStarted,
CONCAT(TRIM(CONCAT(IFNULL(TRIM(TblSurveyor.Name), 'Anonymous'), ' ', IFNULL(TRIM(TblSurveyor.MidInit), ''))), ' ', IFNULL(TRIM(TblSurveyor.SubName), '')) AS Surveyor,
ROUND(AVG(answers.Score), 2) AS Avg, COUNT(answers.RecordID) AS Cnt, ROUND(SUM(answers.Score), 2) AS Sum
FROM answers
LEFT JOIN (attrib_division_rl INNER JOIN division AS TblTier1 ON attrib_division_rl.FieldID = TblTier1.RecordID)
ON answers.AttributionID = attrib_division_rl.AttributionID
LEFT JOIN (attrib_organization_rl INNER JOIN organization AS TblTier2 ON attrib_organization_rl.FieldID = TblTier2.RecordID AND TblTier2.Affiliation = 1)
ON answers.AttributionID = attrib_organization_rl.AttributionID
INNER JOIN (attrib_person_rl INNER JOIN person AS TblTier3 ON attrib_person_rl.FieldID = TblTier3.RecordID AND TblTier3.lnk_job_title = 2)
ON answers.AttributionID = attrib_person_rl.AttributionID
INNER JOIN (surveys LEFT JOIN person AS TblSurveyor ON surveys.SurveyorID = TblSurveyor.RecordID)
ON answers.SurveyID = surveys.RecordID
WHERE surveys.RecordID > 1 AND answers.Score > 0 AND answers.ScaleID = 4
GROUP BY Tier1IDField, Tier2IDField, Tier3IDField, SurveyID
ORDER BY Tier1Field, Tier1IDField, Tier2Field, Tier2IDField, Tier3Field, Tier3IDField, SurveyID, DateStarted