CODEWITHSUNDEEP
mysql
RDowns
RDowns

Reputation: 659

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        |
+-------------+-----------+

Upvotes: 1

Views: 1163

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270763

You need to aggregation after combining the tables:

select player, 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;

Upvotes: 2

Related Questions