Need help with MySQL query getting results to average for year y and y+1

I have a MySQL query:

SELECT px.player, px.pos, px.year, px.age, px.gp, px.goals, px.assists
    , 1000 - ABS(p1.gp - px.gp) - ABS(p1.goals - px.goals) - ABS(p1.assists - px.assists) sim
 FROM hockey p1
 JOIN hockey px
   ON px.player <> p1.player
 WHERE p1.player = 'John Smith'
  AND p1.year = 2010
  HAVING sim >= 900
  ORDER BY sim DESC

This gets me a table of results, something like this:

player  pos year    age gp  goals   assists sim
Player1 LW  2002    25  75  29  32  961
Player2 LW  2000    27  82  29  27  956
Player3 RW  2000    27  78  29  33  955
Player4 LW  2009    26  82  30  30  940
Player5 RW  2001    25  79  33  24  938
Player6 LW  2008    25  82  23  24  936
Player7 LW  2006    27  79  26  33  932

Instead, I would like it to do two things. Average the data and add a player count, so I get something like:

players age gp  goals   assists sim
7   26  79  28  29  945

I tried avg(px.age), avg(px.gp), avg(px.goals)...etc but I am running into errors with my "sim" formula.

Second issue is that underneath that, I would like to have the average of the data for the FOLLOWING year. In other words data from Player1 in 2003, data from Player2 in 2001, etc.

I am stuck as to HOW to get the data to average AND to get it for the fol开发者_运维百科lowing year.

Can anyone help me with either or both of these issues?

---

To get a single subtotal of counts and averages, just wrap your original query AS the inner select... something like... (pq = "PreQuery" select result)

Select 
      max( "Tot Players" ) Players,
      max( "->" ) position,
      count(*) Year,
      avg( pq.age ) AvgAge,
      avg( pq.gp ) AvgGP,
      avg( pq.goals ) AvgGoals,
      avg( pq.assists ) AvgAssists,
      avg( pq.sim ) AvgSim
   from 
      ( SELECT 
             px.player, 
             px.pos, 
             px.year, 
             px.age, 
             px.gp, 
             px.goals, 
             px.assists,
             1000 - ABS(p1.gp - px.gp) 
                  - ABS(p1.goals - px.goals) 
                  - ABS(p1.assists - px.assists) sim  
          FROM 
             hockey p1
                JOIN hockey px ON px.player <> p1.player  
          WHERE 
                 p1.player = 'John Smith'   
             AND p1.year = 2010   
          HAVING 
             sim >= 900
          ORDER BY 
             sim DESC ) pq

If your original query worked, this should get you your overall averages. However, with the INNER query with a having and order, might cause a problem. You might need to kill the order by since it really makes no difference in the outer most query. As for the HAVING clause in the INNER query, might need to be moved to a WHERE pq.sim >= 900 in the OUTER SQL-Select.

Additionally, if you wanted the results of all players first, THEN the total, take your original query and merge it with this one... As you'll see, to keep the columns in synch with BOTH queries, I've put a bogus for player and position so it won't crash on mismatched unions... Notice my COUNT column actually would correspond with the YEAR column of the ORIGINAL query.

For the prior year... As Rob mentioned, you would just do a UNION of the two queries just showing the respective year you were qualifying for in each UNION...

EDIT --- CLARIFICATION for 2nd YEAR....

Per your subsequent comment clarification, you would have to get the basis as the basis of the year +1... if you then want the overall averages again, those would be wrapped to an outer max / avg, etc... But I think THIS is what you want for the subsequent year per player

SELECT
      PrimaryQry.PrimaryPlayer,
      PrimaryQry.PrimaryPos,
      PrimaryQry.PrimaryYear,
      PrimaryQry.PrimaryAge,
      PrimaryQry.PrimaryGP,
      PrimaryQry.PrimaryGoals,
      PrimaryQry.PrimaryAssists,
      PrimaryQry.player,
      PrimaryQry.pos,
      PrimaryQry.year,
      PrimaryQry.age,
      PrimaryQry.gp,
      PrimaryQry.goals,
      PrimaryQry.assists,
      PrimaryQry.sim,
      p2.pos  PrimaryPos2,
      p2.year PrimaryYear2,
      p2.age  PrimaryAge2,
      p2.gp   PrimaryGP2,
      p2.goals PrimaryGoals2,
      p2.assists PrimaryAssists2,
      px2.player player2,
      px2.pos pos2,
      px2.year year2,
      px2.age age2,
      px2.gp gp2,
      px2.goals goals2,
      px2.assists assists2,
      1000 - ABS(p2.gp - px2.gp)
           - ABS(p2.goals - px2.goals)
           - ABS(p2.assists - px2.assists) sim2
  FROM
      ( SELECT
             p1.player PrimaryPlayer,
             p1.pos PrimaryPos,
             p1.year PrimaryYear,
             p1.age PrimaryAge,
             p1.gp PrimaryGP,
             p1.goals PrimaryGoals,
             p1.assists PrimaryAssists,
             px.player,
             px.pos,
             px.year,
             px.age,
             px.gp,
             px.goals,
             px.assists,
             1000 - ABS(p1.gp - px.gp)
                  - ABS(p1.goals - px.goals)
                  - ABS(p1.assists - px.assists) sim             
         FROM
             hockey p1
               JOIN hockey px 
                  ON p1.player <> px.player
         WHERE
                 p1.player = 'John Smith'
             AND p1.year = 2010
         HAVING
             sim >= 900 ) PrimaryQry
         JOIN hockey p2
             ON PrimaryQry.PrimaryPlayer = p2.player
            AND PrimaryQry.PrimaryYear +1 = p2.year
         JOIN hockey px2
             ON PrimaryQry.Player = px2.Player
            AND PrimaryQry.Year +1 = px2.year

If you follow the logic here, you already know the inner query is returning about 10 other players. So, I am keeping the stats of the first person basis IN that query too. THEN, I am joining that result set back to the hockey table TWICE... The join is primary player joined to the first for his/her year +1, the SECOND join works specifically to the one person that qualified against the primary player. The final column results get the entire first year qualifier with the second qualifier, such as

So, it will all be on one row consecutively of 
John Smith 2010   Compare Person 1 YearA  John Smith 2011   Compare Person 1 YearA+1
John Smith 2010   Compare Person 2 YearB  John Smith 2011   Compare Person 2 YearB+1
John Smith 2010   Compare Person 3 YearC  John Smith 2011   Compare Person 3 YearC+1

---

What query are you using to get the averages?

Just applying "AVG" to your expression for 'sim' should work in mysql. e.g.

AVG(1000 - ABS(p1.gp - px.gp) - ABS(p1.goals - px.goals) - ABS(p1.assists - px.assists)) sim

To aggregate over different years, I think there is no alternative to using a subselect or union.

Reference:

• http://dev.mysql.com/doc/refman/5.0/en/subqueries.html
• http://dev.mysql.com/doc/refman/5.0/en/union.html

Something like:

(ORIGINAL AVG QUERY) 
UNION ALL 
(ORIGINAL AVG QUERY WITH NEW YEAR)

should do the trick.

(Note that your original query selects data from every year to compare it to the data for John Smith in 2010, which may not be what you want.)