How do I make three columns combine ordered by the number of goals?-CodePudding

I'm having some problems with unifying two tables in SQL. I want to get the players that scored more goals in the season, but I cannot get it right.

I've done both things separate ways:

SELECT name, surname
FROM players
GROUP BY name, surname

If I do this I get all the names from the players, but if I mix it with the other on a JOIN ON some players disappear with no apparent reason.

RESULT:

| name    | surname   |

| John    | Brown     |
| Robert  | Smith     |
| Albert  | Carpenter |
| David   | Addams    |
| Richard | McAvoy    |

Then I have the number of goals that each one scored, I get this by doing a COUNT(*)

SELECT COUNT(*) AS goals
FROM goals
JOIN players ON (players.id = goals.player_id)
GROUP BY player_id
ORDER BY goals DESC
LIMIT 5;

Down in this bit I can get the desired results as well, which are the 5 highest scores in goals. But I don't know how to get a combined result.

RESULT:

| goals |

| 152   |
| 140   |
| 102   |
| 95    |
| 91    |

I want my columns to be:

| Name    | Surname   | goals |

| John    | Brown     | 152   |
| Robert  | Smith     | 140   |
| Albert  | Carpenter | 102   |
| David   | Addams    | 95    |
| Richard | McAvoy    | 91    |

Any help is appreciated. :)

CodePudding user response：

SELECT name, surname, COUNT(*) AS goals

FROM goals

inner JOIN players ON (players.id = goals.player_id)

GROUP name, surname

ORDER BY goals DESC

LIMIT 5;

CodePudding user response：

Try this

SELECT p.name, p.surname, g.goals
FROM goals g
INNER JOIN players p ON p.id = g.player_id
ORDER BY g.goals DESC LIMIT 5;

Better if you show some records from both table, then it will be easier to answer. Now I'm assuming your goal table is like this playerid -> no. of goals