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:

Subscription

Subscription_Course

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