PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Returning the count in a join but not returning a result for each joined table, possible?

I have this simply query that returns 100 rows, each row is for 1 course:

select c.*
from courses c
  join schools s ON s.id = c.school_id

How can I get the # of students who’s favorited the course, if I try this:

select c.*, st.id
from courses c
  join schools s ON s.id = c.school_id
  join students st ON st.favorite_course_id = c.id

In the above query, if I had say 100 courses, I will get many more results back since I am joining with the students table that has 1000’s of records.

I still want to get back 100 results, 1 row for each course, but I want to know the total # of students who have favourited a particular course.

Tags:

2

Answers


  1. 0

    You can use lateral join like the below :

    select c.*, q.cnt
from courses c
  join schools s ON s.id = c.school_id
  join lateral (select count(*) cnt from students st where st.favorite_course_id = c.id) q on true
    Login or Signup to reply.
  2. 0

    Use a subquery instead of a join:

    SELECT
  c.*,
  (SELECT count(*) FROM students st WHERE st.favorite_course_id = c.id) AS fav_count
FROM courses c
JOIN schools s ON s.id = c.school_id
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search