subquery on two tables with left join

user_17661157 0
Viewed 31

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 */
sql
1 Answers

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
user_46047010
Related