'SQL select two count from one table

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:

COURSE_ID SKILL_ID
1 1
1 2
2 2
2 3
2 4
3 1
4 1
4 2

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
sql


Solution 1:[1]

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;

DB<>Fiddle

Result:

enter image description here

Solution 2:[2]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 JohanB

Related Questions