Premiership table for competition

[cliftonbazaar]

If I have the following data in my database (it shows the results of cricket games) -

homeTeamID - awayTeamID - homePoints - awayPoints

101 - 96 - 10 - 0

97 - 98 - 2 - 4

100 - 99 - 6 - 0

101 100 - 2 - 0

97 - 99 - 0 - 0

96 - 98 - 0 - 10

 

How do I put this data into a premisership table that shows the results as (in descending order of points) -

Team - total points - home points - away points - points against

98 - 14 - 0 - 14 - 2

101 - 12 - 12 - 0 - 0

100 - 6 - 6 - 0 - 2

97 - 2 - 2 - 0 - 4

99 - 0 - 0 - 0 - 6

96 - 0 - 0 - 0 - 20

 

I have tried the looooong way by going through each game with a

 

while($match = mysql_fetch_array($tourny_matches)) {  //Go through all the matches in the tournament
 

 

   loop but I have manged to trip myelf up three times

[Psycho]

What you need is a FULL OUTER JOIN which, unfortunately MySQL does not support. So, you have to be creative

SELECT teamID, SUM(homePoints + awayPoints) AS totalPoints,
       SUM(homePoints) as homePoints, SUM(awayPoints) AS awayPoints,
       SUM(pointsAgainst) AS pointsAgainst
       
FROM (SELECT homeTeamID as teamID, SUM(homePoints) as homePoints, 0 AS awayPoints,
             SUM(awayPoints) as pointsAgainst
      FROM results
      GROUP BY teamID

      UNION ALL

      SELECT awayTeamID as teamID, 0 as homePoints, SUM(awayPoints) AS awayPoints,
             SUM(homePoints) as pointsAgainst
      FROM results
      GROUP BY teamID
     ) as t

GROUP BY teamID
ORDER BY totalPoints DESC, pointsAgainst ASC

 

The key is the UNION of the two inner queries in the FROM clause. The first  gets all the appropriate values where a team was the home team (note that awayPoints is hard coded for 0). The second get all the appropriate values where a team was the away team (note that homePoints is hard coded for 0).

 

Then after those two result sets are combined with UNION ALL, the outer queries combines the home values and away values for each team.

 

NOTE: I originally used just UNION, but couldn't figure out why the results for all the teams was correct except for team ID 96. It was only coming up with 10 for the points against and not 20. The reason is that UNION will drop any exact duplicates between the two queries. And the results for team ID 96 in both those sub queries was 96, 0, 0, 10. So, it dropped one. The UNION ALL was the solution.

Edited March 22, 2013 by Psycho

[cliftonbazaar]

Thanks for all that, how would I go about finding out how many home and away games they have each played (because sometimes not all teams have played the same amount of games). The best I could come up with was

SELECT teamID,
            SUM(homeGames + awayGames) AS gamesPlayed,
            SUM(homeGames) AS homeGames,
            SUM(awayGames) AS awayGames,
            SUM(homePoints + awayPoints) AS totalPoints,
            SUM(homePoints) as homePoints, SUM(awayPoints) AS awayPoints,
            SUM(pointsAgainst) AS pointsAgainst
       
        FROM (
            SELECT COUNT(homeTeamID) AS homeGames, homeTeamID as teamID, SUM(homePoints) as homePoints, 0 AS awayPoints,
                SUM(awayPoints) as pointsAgainst
              FROM matches
              GROUP BY teamID

            UNION ALL

            SELECT COUNT(awayTeamID) AS awayGames, awayTeamID as teamID, 0 as homePoints, SUM(awayPoints) AS awayPoints,
                SUM(homePoints) as pointsAgainst
            FROM matches
            GROUP BY teamID
        ) as t
        GROUP BY teamID
        ORDER BY totalPoints DESC, pointsAgainst ASC

but it gives an error

Edited March 23, 2013 by cliftonbazaar

[MargateSteve]

Are you also storing the match results in the database or just the points? I have set-up a few league tables for sites where the table is created on the fly with php from results stored in the database. Although I have not tried to get it to show points against yet, it should not be too much of an issue.

 

If this would be of any use I can post the code.

 

Steve

[Psycho]

Thanks for all that, how would I go about finding out how many home and away games they have each played (because sometimes not all teams have played the same amount of games). The best I could come up with was

 

You would save us both a lot of time if you would state all of your requirements up front. It's a little disheartening to put time and effort into helping someone only to have them come back and say "what I really want is . . . "

 

But, what you are asking for now is trivial based upon the previous solution I provided. Just add counts for homeGames and awayGames to both the sub queries. Since the first query is for the home game data, use COUNT(teamID) to get the number of home games and hard code awayGames as 0. Then do the opposite for the other sub query. Then, lastly, do a sum on the values in the main query.

 

 

SELECT teamID, SUM(homePoints + awayPoints) AS totalPoints,
       SUM(homePoints) as homePoints, SUM(awayPoints) AS awayPoints,
       SUM(homeGames) as homeGames, SUM(awayGames) AS awayGames,
       SUM(pointsAgainst) AS pointsAgainst
       
FROM (SELECT homeTeamID as teamID,
             SUM(homePoints) as homePoints, 0 AS awayPoints,
             COUNT(homeTeamID) as homeGames, 0 AS awayGames,
             SUM(awayPoints) as pointsAgainst
      FROM results
      GROUP BY teamID

      UNION ALL

      SELECT awayTeamID as teamID,
             0 as homePoints, SUM(awayTeamID) AS awayPoints,
             0 AS homeGames, COUNT(awayTeamID) as awayGames,
             SUM(homePoints) as pointsAgainst
      FROM results
      GROUP BY teamID
     ) as t

GROUP BY teamID
ORDER BY totalPoints DESC, pointsAgainst ASC

[cliftonbazaar]

You would save us both a lot of time if you would state all of your requirements up front. It's a little disheartening to put time and effort into helping someone only to have them come back and say "what I really want is . . . "

Sorry about that, it was only after I had it all working that a tester said "but my team hasn't played as many games as his" that I realised that I needed to be able to show the number of games each team has played.

 

Thanks again for the solution

[cliftonbazaar]

So here is my final code -

SELECT teamID,
			SUM(homeGames + awayGames) as gamesPlayed,
			SUM(homeGames) as homeGames,
			SUM(awayGames) as awayGames,
			SUM(homePoints + awayPoints) AS totalPoints,
    		SUM(homePoints) as homePoints, SUM(awayPoints) AS awayPoints,
    		SUM(pointsAgainst) AS pointsAgainst
  		FROM (
			SELECT homeTeamID as teamID, tournament,
				SUM(homePoints) as homePoints, 0 as awayPoints,
				COUNT(homeTeamID) as homeGames, 0 as awayGames,
				SUM(awayPoints) as pointsAgainst
      		FROM matches
			WHERE tournament=$tournament and completed
	      	GROUP BY teamID

			UNION ALL

			SELECT awayTeamID as teamID, tournament,
				0 as homePoints, SUM(awayPoints) AS awayPoints,
				COUNT(awayTeamID) as awayGames, 0 as homeGames,
				SUM(homePoints) as pointsAgainst
			FROM matches
			WHERE tournament=$tournament and completed
			GROUP BY teamID
		) as t
		GROUP BY teamID
		ORDER BY totalPoints DESC, pointsAgainst ASC

one problem - it states that all games were played at 'home'.
It gets the correct amount of games, but says they were all played at 'home' and none played 'away' - yet it gets the 'home' and 'away' points correct!

 

Output code (PHP) is

	while($display_ladder = mysql_fetch_array($ladder)){	
$notes .= "<br>".$display_ladder['teamID']." - ".$display_ladder['gamesPlayed']." - ".$display_ladder['homeGames']."/".$display_ladder['awayGames']." - ".$display_ladder['totalPoints']." - ".$display_ladder['homePoints']." - ".$display_ladder['awayPoints']." - ".$display_ladder['pointsAgainst']; 	
	}

so it's not just a simple spelling error in the PHP code.

[Psycho]

That is because you changed the query from what I provided.

 

The UNION clause has some specific requirements.

 

1) The queries must have the same number of fields (which is why I hard coded some fields with a 0).

2) The "same" fields from the queries must be of the same type

3) The "same" fields must be in the same order.

 

In the query I provided, homeGames came before awayGames in the two sub queries. However, you changed the second sub query to have awayGames come before homeGames. In truth, you don't even need to name the columns for the second sub query since the results will be based upon the column names from the first query. I only name them to make it easier to read. So, in your version, the awayGames are considered homeGames because they are in the column position associated with homeGames in the first query.

 

 

SELECT teamID,
       SUM(homeGames + awayGames) as gamesPlayed,
       SUM(homeGames) as homeGames, SUM(awayGames) AS awayGames,
       SUM(homePoints + awayPoints) AS totalPoints,
       SUM(homePoints) as homePoints, SUM(awayPoints) AS awayPoints,
       SUM(pointsAgainst) AS pointsAgainst
       
FROM (SELECT homeTeamID as teamID,
             COUNT(homeTeamID) as homeGames, 0 AS awayGames,
             SUM(homePoints) as homePoints, 0 AS awayPoints,
             SUM(awayPoints) as pointsAgainst
      FROM results
      GROUP BY teamID

      UNION ALL

      SELECT awayTeamID as teamID,
             0 AS homeGames, COUNT(awayTeamID) as awayGames,
             0 as homePoints, SUM(awayTeamID) AS awayPoints,
             SUM(homePoints) as pointsAgainst
      FROM results
      GROUP BY teamID
     ) as t

GROUP BY teamID
ORDER BY totalPoints DESC, pointsAgainst ASC

Edited March 25, 2013 by Psycho

[cliftonbazaar]

Thanks for that - out of all the research I did on google not one site told me

3) The "same" fields must be in the same order.

 

Everything works 100% now