Premiership table for competition

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

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, 22 March 2013 - 01:19 AM.

The quality of the responses received is directly proportional to the quality of the question asked.

I do not always test the code I provide, so there may be some syntax errors. In 99% of all cases I found the solution to your problem here: http://www.php.net

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

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

If I have worded things badly, used incorrect formatting or confused the hell out of you just let me know. I am new to php/mysql and am struggling with the terminology, let alone the etiquette!MY INTRODUCTION

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

The quality of the responses received is directly proportional to the quality of the question asked.

I do not always test the code I provide, so there may be some syntax errors. In 99% of all cases I found the solution to your problem here: http://www.php.net

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.

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!

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