# Premiership table for competition

Go to solution Solved by Psycho,

## Recommended Posts

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

##### Share on other sites

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 by Psycho
##### Share on other sites

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 by cliftonbazaar
##### Share on other sites

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

##### Share on other sites

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```
##### Share on other sites

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

##### Share on other sites

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)){
}
```

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

##### Share on other sites

• Solution

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 by Psycho
##### Share on other sites

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

##### Share on other sites

This thread is more than a year old. Are you sure you have something important to add to it?

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

×   Pasted as rich text.   Restore formatting

Only 75 emoji are allowed.