Average of best three scores
I have this query,
SELECT i.Name, PistolComp, ROUND(AVG(c.Phase1+c.Phase2+c.Phase3+c.Phase4+c.Phase5+c.Phase6),2) AS "Average Score",
(CASE
WHEN PistolCompsID = '38' THEN CONCAT(ROUND(AVG(((c.Phase1+c.Phase2+c.Phase3+c.Phase4+c.Phase5+c.Phase6)/400)*100),2),' %')
WHEN PistolCompsID = '36' THEN CONCAT(ROUND(AVG(((c.Phase1+c.Phase2+c.Phase3+c.Phase4+c.Phase5+c.Phase6)/600)*100),2),' %')
WHEN PistolCompsID = '5' THEN CONCAT(ROUND(AVG(((c.Phase1+c.Phase2+c.Phase3+c.Phase4+c.Phase5+c.Phase6)/600)*100),2),' %')
WHEN PistolCompsID = '4' THEN CONCAT(ROUND(AVG(((c.Phase1+c.Phase2+c.Phase3+c.Phase4+c.Phase5+c.Phase6)/600)*100),2),' %')
WHEN PistolCompsID = '24' THEN CONCAT(ROUND(AVG(((c.Phase1+c.Phase2+c.Phase3+c.Phase4+c.Phase5+c.Phase6)/600)*100),2),' %')
WHEN PistolCompsID = '42' THEN CONCAT(ROUND(AVG(((c.Phase1+c.Phase2+c.Phase3+c.Phase4+c.Phase5+c.Phase6)/600)*100),2),' %')
ELSE 'Unspecified' END) AS "Average as Percent"
FROM joo_fgcPistolPracticeDetail c, joo_fgcPistolComps e, joo_fgcPistolPractice g, joo_fgcMembers i
WHERE e.PistolCompsID = g.PistolCompetition AND g.PistolPracticeID = c.PistolPID AND i.MemberID = c.ShooterID AND e.ISSF = 1 AND g.ShootClassID = '2' AND YEAR(g.PDate)='2016' AND (SELECT COUNT(*)
FROM joo_fgcPistolPracticeDetail b
WHERE b.ShooterID = c.ShooterID AND b.PistolPID=c.PistolPID AND (b.Phase1+b.Phase2+b.Phase3+b.Phase4+b.Phase5+b.Phase6)>=(c.Phase1+c.Phase2+c.Phase3+c.Phase4+c.Phase5+c.Phase6))<=3
GROUP BY ShooterID
HAVING COUNT(*)>2
ORDER BY ShooterID, (AVG(c.Phase1+c.Phase2+c.Phase3+c.Phase4+c.Phase5+c.Phase6)) DESC
but it is averaging all scores not just limiting to the best three, can anyone see why and explain to me where I am going wrong?