I have a table with columns: COURSE_ID (int) SKILL_ID (int)

One course could have many skills, for example the table could contain values:

The result should show count of courses and count of skills they have. For example for the table above the result should be:

1 = 1 (course 3 has 1 skill) (count course with 1 skill = 1)

2 = 2 (course 1 and 4 have 2 skills) (count course with 2 skill = 2)

3 = 1 (course 2 has 3 skills) (count course with 3 skill = 1)

Could anybody help with this query?

And one more question. I tried to execute this query and I am expecting one number with count of courses with 6 skills, but I got many records (in fact - rows count = expected result, value in rows = 6), can't understand why, could anybody explain?

select count(table.course_id) from Table table
GROUP BY table.course_id
HAVING COUNT(table.skill_id) = 6

CodePudding user response：

Try

select table.course_id, count(table.skill_id)
from table_name table
GROUP BY table.course_id

And your query should be

select table.course_id, count(table.skill_id)
from table_name table
GROUP BY table.course_id
HAVING COUNT(table.skill_id) = 6

CodePudding user response：

It's not entirely clear what you're expecting as a result but I think this is what you're after

select Skill, Count(*) courseCount
from (
    select course_id, Count(distinct SKILL_ID) Skill
    from t
    group by COURSE_ID
)s
group by Skill;