How to add a condition to count function in PostgreSQL

Question

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.

eshirvana · Accepted Answer · 2021-11-28 20:02:01Z

1

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

edited Nov 28, 2021 at 20:02

answered Nov 28, 2021 at 19:54

eshirvana

24.7k3 gold badges28 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Henry · Accepted Answer · 2021-11-28 20:00:12Z

1

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

answered Nov 28, 2021 at 20:00

Henry

15.9k7 gold badges51 silver badges69 bronze badges

Collectives™ on Stack Overflow

How to add a condition to count function in PostgreSQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related