Counting number of user skills per user

user_2158895 0
Viewed 39

I have three tables users, skills and user_skills

users table with columns:

id,name,sex

skills table with columns:

id,name,view_count

and user_skills table with columns:

id,user_id,skill_id

I want to output total count of skills based on user selection which are store on user_skill expected output table is:

Skill name user_skill_count
Programming 8
Database 12
Accounts 9
Analysis 12

I have tried this SQL query

select `skills`.*, (select count(*) from `user_skills`
                    where `skills`.`id` = `user_skills`.`skill_id`) as `user_skill_count`
from `skills`

but it returns other skills which user has not selected and output count 0 as table below

Skill name user_skill_count
Programming 8
Database 0
Accounts 9
Analysis 0

I want the results to be only the skill users has selected.

mysql sql
2 Answers

Just use a simple join in your query:

SELECT `skills`.`name`, count(*) as `user_skill_count`
FROM `skills`
INNER JOIN `user_skills` ON `skills`.`id` = `user_skills`.`skill_id`
GROUP BY `skills`.`id`, `skills`.`name`
user_16902170
user_148362890
Related