Home > Software engineering >  How to add a condition to count function in PostgreSQL
How to add a condition to count function in PostgreSQL

Time:11-29

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
  • Related