MySQL Forums
Forum List  »  Newbie

Average of best three scores
Posted by: Nevin Middleton
Date: September 05, 2017 07:25AM

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?

Options: ReplyQuote


Subject
Written By
Posted
Average of best three scores
September 05, 2017 07:25AM
September 05, 2017 08:54AM


Sorry, only registered users may post in this forum.

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.