title: mysql average of best three
body: <p>I have pieced this together from sites online and it works but not completely, what i need it to do is take the top 3 results and average them but it takes ALL results, can anyone point me in the right direction?</p>
  
  <pre><code> SELECT i.NAME,
     e.comp,
     Round(Avg(c.phase1 + c.phase2 + c.phase3 + c.phase4 + c.phase5
               + c.phase6), 2) AS "Average Score",
     ( CASE
         WHEN compID = '7' THEN Concat(Round(Avg(
                                                ( (
         c.phase1 + c.phase2 + c.phase3 + c.phase4 + c.phase5
         + c.phase6 ) / 400 ) * 100), 2), ' %')
         WHEN compID = '5' THEN Concat(Round(Avg(
                                                ( (
         c.phase1 + c.phase2 + c.phase3 + c.phase4 + c.phase5
         + c.phase6 ) / 600 ) * 100), 2), ' %')
         WHEN compID = '3' 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   jos_practicedetail c,
     jos_comps e,
     jos_practice g,
     jos_members i
  WHERE  e.compsid = g.competition
     AND g.practiceid = c.practicepid
     AND i.memberid = c.competitorid
     AND g.typeID = '2'
     AND Year(g.pdate) = '2017'
     AND (SELECT Count(*)
          FROM   jos_practicedetail b
          WHERE  b.competitorid = c.competitorid
                 AND b.practicepid = c.practicepid
                 AND ( b.phase1 + b.phase2 + b.phase3 + b.phase4 + b.phase5
                       + b.phase6 ) &gt;= (
                     c.phase1 + c.phase2 + c.phase3 + c.phase4 + c.phase5
                     + c.phase6 )) &lt;= 3
  GROUP  BY competitorid
  HAVING Count(*) &gt; 2
  ORDER  BY competitorid,
        ( Avg(c.phase1 + c.phase2 + c.phase3 + c.phase4 + c.phase5
              + c.phase6) ) DESC  
  </code></pre>