CODEWITHSUNDEEP
mysqlgroup-bysql-order-byunion
James Clifton
James Clifton

Reputation: 175

How to resolve this problem with aggregates and UNION in MySQL?

If my data is this

match homeTeam  awayTeam  homeTeamID  awayTeamID  homePoints awayPoints
  1    Alpha      Beta        1           2           4          2
  2    Gamma      Delta       3           4           6          0
  3    Alpha      Gamma       1           3           2          4
  4    Delta      Beta        4           2           3          3

I need to make a ladder for them but I can't get it just right

The results should look like this

Name  played  Points
Gamma    2      10
Alpha    2       6
Beta     2       5
Delta    2       3

So far my code looks like this

$query = "SELECT *
    FROM (
        SELECT homeTeam AS teamName,
            COUNT(homeTeamID) AS matches_played,
            SUM(if(homeTeamID, homePoints, 0)) AS total_points
            FROM matches
        UNION ALL SELECT awayTeam AS teamName,
            COUNT(awayTeamID) AS matches_played,
            SUM(if(awayTeamID, awayPoints, 0)) AS total_points
            FROM matches
        ) all_points
    GROUP BY teamName
    ORDER BY total_points DESC  ";

but all it did was show ALPHA played 4 games for 15 points and BETA played 4 games for 9 points - Gamma & Delta were gone :(

Upvotes: 0

Views: 113

Answers (1)

NiematojakTomasz
NiematojakTomasz

Reputation: 2473

You do not have GROUP BY clause in inner query. But it's still incorrect. try:

SELECT teamName, COUNT(*) AS played, SUM(points) as points
    FROM (
        SELECT homeTeam AS teamName,
            homePoints AS points
            FROM matches
        UNION ALL SELECT awayTeam AS teamName,
            awayPoints AS points
            FROM matches
        ) all_points
    GROUP BY teamName
    ORDER BY total_points DESC

Upvotes: 4

Related Questions