MySQL grouping with a comma separated field

Question

Here's a tricky one.

I have data as follows:

User ID  Name     Skill Sets
1        Jim      Install, Configure
2        Jack     Install
3        John     Configure, Setup, Blah
4        Bill     Setup, Install

This isn't my design, and it's not an option to change the way the data is formatted. The trouble is that I need to group by the unique skill sets. Obviously a group by right now gives me:

Skill Set                Count
Install, Configure       1
Install                  1
Configure, Setup, Blah   1
Setup, Install           1

Desired Output is:

Skill Set    Count
Install      3
Configure    2
Setup        2
Blah         1

Any Ideas? I could conceivably make a view that separates the skill sets into a normalized form (as it should be). But I'm not positive on the syntax for that either.

Answer 1

You need to have a rowset containing all possible values of your skills.

MySQL lacks a way to do it, so you'll have to generate it somehow.

If you have such a resultset, just issue:

SELECT  skill, COUNT(*)
FROM    (
        SELECT 'Install' AS skill
        UNION ALL
        SELECT 'Configure' AS skill
        UNION ALL
        SELECT 'Setup' AS skill
        UNION ALL
        SELECT 'Blah' AS skill
        ) s
JOIN    users u
ON      find_in_set(s.skill, u.sets)
GROUP BY
        s.skill

Since you mentioned that you have your skills in a separate table, use that table:

SELECT  skill, COUNT(*)
FROM    skills s
JOIN    users u
ON      find_in_set(s.skill, u.sets)
GROUP BY
        s.skill

This, however, will not ever match the typos, they will be just skipped.