How to count the number of times an attribute appears in an inner query?
Something like this.

The below lists everything: which student took which course  taught by which professor.

select
  s.student_id, c.course_id, p.professor_id
from
  students s
  join takes_course tc on (s.student_id = tc.student_id)
  join course c on (c.course_id = tc.course_id)
  join professor p on (p.professor_id = c.professor_id)
;


But we do not care about courses. The below lists which student is taught by which professor(s). (student_id, course_id) is unique, and 1 course is taught by 1 professor, so the same (student_id, professor_id) values appear multiple times, if the student attended multiple courses taught by the same professor.

select
  s.student_id, p.professor_id
from
  students s
  join takes_course tc on (s.student_id = tc.student_id)
  join course c on (c.course_id = tc.course_id)
  join professor p on (p.professor_id = c.professor_id)
;


We do not care about professor_id and we need to list students taught by more than 1 course.

select
  s.student_id
from
  students s
  join takes_course tc on (s.student_id = tc.student_id)
  join course c on (c.course_id = tc.course_id)
  join professor p on (p.professor_id = c.professor_id)
group by
  s.student_id, p.professor_id
having
  count(*) &gt; 1
;


This may list the same student multiple times because a student can take 2 courses from a professor and 3 courses from another professor, so list 1 student only once:

select
  distinct s.student_id
from
  students s
  join takes_course tc on (s.student_id = tc.student_id)
  join course c on (c.course_id = tc.course_id)
  join professor p on (p.professor_id = c.professor_id)
group by
  s.student_id, p.professor_id
having
  count(*) &gt; 1
;