RDowns RDowns - 13 days ago 8
MySQL Question

How to query this table to find player who scored most amount of goals?

How can I query this table

results
to find out which
player_id
has scored the most amount of goals in a particular
community_id
?

+----------------+-------------+-----+
| Field | Type | Key |
+----------------+-------------+-----+
| results_id | int | Pri |
| community_id | int | |
| player1_id | int | |
| player1_goals | int | |
| player2_id | int | |
| player2_goals | int | |
+----------------+-------------+-----+


NOTE: a player can either be player1 or player2 so their goals have to be added up accordingly, i.e:

SELECT Player ID, count (*) Goals FROM results
(SELECT SUM (player1_goals) WHERE player1_id = 2 AND community_id = 5)
(SELECT SUM (player2_goals) WHERE player2_id = 2 AND community_id = 5) AS player_id GROUP BY Player ID ORDER BY Goals DESC


But I know that syntax is terribly incorrect...

My desired output is:

+-------------+-----------+
| Player ID | Goals |
+-------------+-----------+
| 14 | 64 |
| 8 | 43 |
| 7 | 17 |
+-------------+-----------+

Answer

You need to aggregation after combining the tables:

select play, sum(goals)
from ((select player1_id as player, player1_goals as goals
       from results
       where community_id = 5
      ) union all
      (select player2_id as player, player2_goals as goals
       from results
       where community_id = 5
      )
     ) p
group by player
order by sum(goals) desc
limit 1;