Problem adding a number of students registered to a course (as a row) to a SELECT statement in POSTGRES
I have a database of courses. I need to get a name, a a topic, a teacher, a duration and a number of students registered. I get the first four successfully, but not the last one.
Here is what my tables look like:
That's the successful part for the first four:
SELECT c.name, t.topic_name AS topic, u.name || ' '|| u.surname AS TEACHER, ((c.end_date - c.start_date) / 7)::int AS duration FROM public.course c RIGHT JOIN public.topic t ON c.topic_id = t.topic_id RIGHT JOIN public.teacher_course tc ON c.course_id = tc.course_id RIGHT JOIN public.user u ON tc.teacher_id = u.user_id WHERE u.role_id = 2;
Basically, to know the number of registered students per course, I only need to count records in the journal table for each course, but when I add
count(j.id_record) AS students_registered
it just breaks and asks me to group everything by and blah blah. I'm confused about that. How to get this number correctly for each course?