I have these tables Course, subscription,subscription_Course(A table that creates a relation between Course and subscription), and another with Student. I want to Select all the id_courses that have a subscription count higher than 1 but only want to count the subscriptions from different students. Example: If a Student Subscribes two times the same course I want to have a condition that enables the count function to not count more than one time in these cases
These are my tables:
Student:
idStudent(pk) | cc | nif |
---|---|---|
1 | 30348507 | 232928185 |
2 | 30338507 | 231428185 |
3 | 30438507 | 233528185 |
4 | 30323231 | 3232132 |
Subscription
idsubscription(pk) | Student(fk) | value_subscription | vouchercurso | date |
---|---|---|---|---|
1 | 1 | 100 | null | 2021-11-01 |
2 | 2 | 150 | null | 2021-12-11 |
3 | 3 | 160 | null | 2021-01-03 |
4 | 4 | 500 | null | 1996-11-07 |
5 | 1 | 900 | null | 2001-07-05 |
6 | 2 | 432 | null | 2021-05-09 |
Subscription_Course
idsubscription(PK/fk) | id_Course(pk/fk) | Grade |
---|---|---|
1 | 3 | 9 |
2 | 4 | 15 |
3 | 5 | 12 |
6 | 3 | 9 |
5 | 4 | 16 |
2 | 6 | 20 |
6 | 5 | 4 |
For example, when counting within my table Subscription_Course only the id_course:5 would have a count higher than 1 because 3 and 4 have a subscription from the same student.
I have this query for now:
Select id_Course
From Subscription_Course
Group by id_Course
Having Count (id_Course)>1
I don't know what to do to add this condition to the count.
CodePudding user response:
seems like you need to join to Subscription and count unique Student id's:
select id_Course
from Subscription_Course sc
join Subscription s
on s.idsubscription = sc.idsubscription
group by id_Course
having Count(distinct Studentid)>1
CodePudding user response:
You can join the Subscription_Course
table with the Subscription
table in order to access the id_Student
column. Then just count the distinct id_Student
values for each id_Course
value.
SELECT
Subscription_Course.id_Course,
COUNT(DISTINCT Subscription.id_Student) AS student_count
FROM Subscription_Course
INNER JOIN Subscription
ON Subscription_Course.id_Subscription = Subscription.id_Subscription
GROUP BY Subscription_Course.id_Course
HAVING COUNT(DISTINCT Subscription.id_Student) > 1
ORDER BY student_count DESC;
With result:
id_course | student_count
----------- ---------------
3 | 2
4 | 2
5 | 2