MySQL Forums
Forum List  »  General

Get AVG, COUNT & SUM from multiple groups
Date: September 29, 2022 11:08AM

Below is a sql statement with 4 GROUP BY fields.
In the field list is an AVG, COUNT & SUM.

The statement below only returns AVG, COUNT & SUM from the last group (SurveyID).

What I need is the AVG, COUNT & SUM for each of the 4 groups.
What is the most efficient way I can do that???

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

Options: ReplyQuote

Written By
Get AVG, COUNT & SUM from multiple groups
September 29, 2022 11:08AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.