CODEWITHSUNDEEP
mysql
JVMX
JVMX

Reputation: 1078

Ordering based on the total of a related table in mySql

I have a number of baseball players. They have played in a number of baseball games. I wish to find the players on the Yankees and order them by the number of hit they have. They are in a MySqlDatabase.

Here is the basic structure:

[Players]
ID      Name      Team
1       Jim       Yankees
2       Bob       Yankees
3       Ted       Red Socks
4       Hal       Yankees
5       Tom       Dodgers


[Games]
ID     Player_Id     Hits
1      1             2
2      2             0 
3      3             3 
4      4             1 
5      5             2  
6      1             0
7      2             0 
8      3             1 
9      4             4 
10     5             1 
11     1             0
12     2             0 
13     3             1 
14     4             3 
15     5             1

I am looking for the results

Hal      8
Jim      2
Bob      0

What is the query?

Upvotes: 0

Views: 34

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Here is what I would consider the better solution:

SELECT p.Name, sum(Hits) as TotalHits
FROM Players p LEFT OUTER JOIN
     Games g
     on p.id = g.player_id
WHERE p.Team = 'Yankees'
GROUP BY p.id
ORDER BY TotalHits DESC;

What do I consider this better?

  • In MySQL, a subquery is materialized. This results in unnecessary overhead.
  • By not using a materialized view, MySQL has a better opportunity to use indexes for the join.
  • The use of LEFT OUTER JOIN guarantees that all Yankees players will be included, even those not in the Games table.

Upvotes: 2

Mudassir Hasan
Mudassir Hasan

Reputation: 28751

SELECT playerName ,totalHits
FROM players p
INNER JOIN

(
SELECT player_ID,SUM(Hits) as totalHits
FROM games
GROUP BY player_ID
) as g

ON p.ID=g.player_ID
WHERE p.team='Yankees'
ORDER BY totalHits desc

Upvotes: 1

Related Questions