CODEWITHSUNDEEP
sqlmysqlsubquery
skeggse
skeggse

Reputation: 6323

MySQL Join Three Tables and Average

I've been puzzling over how to structure a special query, and the best I can figure out is that it would have to be some kind of complex subquery system.

I'm not entirely sure, though.

I have three tables, teams, matches, and scored.

The teams table contains a list of team numbers and corresponding team names.

The matches table contains a list recorded match results (one row per team per match), with the corresponding team number.

The scored table contains a list of all of the information about each score the team made, and the corresponding match result id.

┌────────────┐
| TEAMS      |
├────────────┤
│ teamnumber |
│ teamname   |
└────────────┘

┌───────────────┐
| MATCHES       |
├───────────────┤
│ teamnumber    |
│ matchresultid |
└───────────────┘

┌───────────────┐
| SCORED        |
├───────────────┤
│ matchscoredid |
│ matchresultid |
└───────────────┘

Given a teamnumber, I need to get the average number of rows in scored per matchresultid. How would I do that?

Upvotes: 1

Views: 4250

Answers (4)

DRapp
DRapp

Reputation: 48139

I think the one element you left out that may have confused others is the actual score of the game a given team was playing against. So basically you want one team, and through the course of the season, you want the average # points they scored...

select 
      t.teamnumber,
      t.teamname,
      avg( s.totalpoints ) TeamAvgPoints
   from 
      teams t
         join matches m
            on t.teamnumber = m.teamnumber
            join scored s
               on m.matchresultid = s.matchresultid
   where 
      t.teamnumber = SomeValue

If you want a comparison of ALL teams averages, ignore the WHERE clause and just do a group by...

   group by
      t.teamnumber, 
      t.teamname

If the scored table has multiple rows per game, then a pre-aggregate would need to be done per game to have total points first, THEN get the average... something like

select
      PreQuery.TeamNumber,
      PreQuery.TeamName,
      avg( ifnull( PreQuery.MatchTotalPoints, 0 ) ) AvgPerGame
   from 
      ( select 
            t.teamnumber,
            t.teamname,
            m.matchresultid,
            count(*) MatchTotalPoints
         from 
            teams t
               left join matches m
                  on t.teamnumber = m.teamnumber
                  left join scored s
                     on m.matchresultid = s.matchresultid
         where 
            t.teamnumber IN( SomeValue, AnotherValue, MoreValues, EtcValue )
         group by
            t.teamnumber,
            t.teamname,
            m.matchresultid ) PreQuery
   group by 
       PreQuery.TeamNumber,
       PreQuery.TeamName

In THIS version of the query, if you wanted to compare all teams, remove the inner WHERE clause for a specific team, and apply the group by to the OUTER query by teamnumber and teamname.

To get your extra qualifier for the entire list, I've changed to LEFT joins, and finished with an IFNULL() for the average...

Upvotes: 3

Borja
Borja

Reputation: 2198

Do you need the number of scores or the score average? If you need the score average, you need to replace the "count" with "avg"

SELECT s.matchresultid, COUNT(s.matchscoredid) 
  FROM matches m INNER JOIN scored s ON m.matchresultid = s.matchresultid
 WHERE m.teamnumber = <team>
GROUP BY s.matchresultid

Upvotes: 1

James Sumners
James Sumners

Reputation: 14777

SELECT a.teamname team, b.matchresultid match, COUNT(c.matchscoredid) scores 
FROM teams a, matches b, scored c 
WHERE a.teamnumber = b.teamnumber AND b.matchresultid = c.matchresultid;

That should return the number of scores for each team per match.

Upvotes: 0

LukLed
LukLed

Reputation: 31842

Try this:

SELECT s.matchresultid, count(*) 
FROM scored s
JOIN matches m
ON m.matchresultid = s.matchresultid
WHERE m.teamnumber = your_team_number
GROUP BY s.matchresultid

Upvotes: 0

Related Questions