MySQL query with matching and sum - League Table
Ally
I have 5 tables:
- Players (id, name, team_id)
- Teams (id, name)
- Results (id, game_id, player.id, position)
- Games (id, location)
- Score (position, score)
as follows
I want to make a league table like this
I am confused how to sum up the scores for each player. This is my code so far:
SELECT Players.name, Teams.name, Results.position FROM Players
INNER JOIN Teams ON Players.team_id = Teams.id
JOIN Results ON Player.id = Results.player_id
JOIN Scores ON Results.position = Scores.scores
Any idea?
Juan Pablo Califano
To list all the players (whether they appear on the results table or not) you have to use a LEFT JOIN.
SELECT p.name player_name,t.name team_name,SUM(COALESCE(s.score,0)) as total_score
FROM players p
INNER JOIN teams t ON t.id = p.team_id
LEFT JOIN results r ON p.id = r.player_id
LEFT JOIN scores s ON s.position = r.position
GROUP BY p.id
ORDER BY total_score desc
Collected from the Internet
Please contact [email protected] to delete if infringement.
edited at
Related
Related Related
- 1
MySQL league table
- 2
How to order a sports league table in MySQL?
- 3
MySQL query, sum data from table
- 4
MySQL: Update all rows in 2 table matching results of another query
- 5
Optimal MYSQL query for longest prefix matching in a table with 5 million rows
- 6
MySQL Join; Query only non-matching table fields
- 7
Optimal MYSQL query for longest prefix matching in a table with 5 million rows
- 8
MYSQL Query matching column names to rows in another table
- 9
Mysql query for sum two table data by product wise
- 10
MySQL Sum and Case Query
- 11
Mysql query SUM group?
- 12
MySQL query entire table using 1 of 2 given matching parameters in first query
- 13
Mysql query sum query in codeigniter
- 14
MYSQL: Return all rows in one table along with the sum of matching rows in another
- 15
Display a league table on a webpage?
- 16
MySQL sum with multiple table
- 17
PDO SUM MYSQL table
- 18
MySQL multiple table sum
- 19
MySQL query not matching due to punctuation
- 20
Fast String Matching MySQL Query
- 21
Pattern matching in MySQL database query
- 22
How to generate a query which consider data from one table and correspondingly fill 0 for non matching fields in mysql
- 23
How to generate a query which consider data from one table and correspondingly fill 0 for non matching fields in mysql
- 24
How to get the sum of the sum with one mysql query
- 25
Squash League Results - SQL Query
- 26
Need help on MYSQL QUERY (SUM)
- 27
MySQL query to get the sum of a column
- 28
MySQL: Sum () and Count in one query?
- 29
Mysql query sum and find average
Comments