subquery on two tables with left join-CodePudding

I have two tables, a players table, and a skills table. I want to find the most common skill type in the players table. I am trying to practice using sub-queries. How would a subquery for an example like this work? Below are the tables and my attempt.

Players table

 ------------- ------------------ 
| player_name | player_skills_id |
 ------------- ------------------ 
| player 1    | 1                |
| player 2    | 2                |
| player 3    | 2                |
| player 4    | 1                |
| player 5    | 1                |
| player 6    | 1                |
 ------------- ------------------

Skills table

 ------------------ --------------- 
| player_skills_id | player_skills |
 ------------------ --------------- 
| 1                | bad           |
| 2                | good          |
| 3                | great         |
 ------------------ ---------------

The table I want:

 --------------- ------- 
| player skills | count |
 --------------- ------- 
| bad           | 4     |
| good          | 2     |
| great         | 0     |
 --------------- -------

My attempt:

SELECT players.player_name 
FROM players 
LEFT JOIN skills
ON players.player_skills_id = skills.player_skills_id;
/* I am not sure how the subquery part works from here */

CodePudding user response：

This will list every skill level together with a count for the number of players at each level.

SELECT s.player_skills, COUNT(*) AS count
FROM skills s
LEFT JOIN players p ON s.player_skills_id = p.player_skills_id
GROUP BY s.player_skills_id