HI i have skills tables as below
Various user add skills to their profile. Now i want to list all the skills decreasing order of their uses. Like as below
Php(10) , ASP (5) , Perl(1)
Its means 10 user added php as their skill, 5 user ASP etc.
I have stored the skills in user table in skills column with comma separated
Try this:
select id, name
from (
select *, (select sum(1) from user u where find_in_set(s.id, u.skills)) as cnt
from skills s
) t
order by cnt desc
-- limit 20