Select entries that appear more than 4 times

I have 2 tables, one contains people and another contains enrolments with a reference to the people id’s. I have to create a view where only results that appear in the enrolments more than 4 times are included.

I think I need to use count() but I can’t get it to count what I need it to. How do you output a table that only includes the people that appear in the enrolment table more than 4 times?

Here are some sample rows and what I tried to do:

Table enrolment:

Students 1010093 and 1010138 would fit the criteria because they appear more than 4 times. But there are many students with fewer entries.

Table people:

(id is the id that enrolment refers to in student column).

My attempt was this:

create or replace view Q1(uniid,name) as
select people.uniid, people.name
from people left outer join enrolments on (people.id = enrolments.student)
group by people.uniid, people.name having count(enrolments.student) > 4;

Sample output:

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

Aggregate enrolments, filter, and only then join to people:

CREATE OR REPLACE VIEW q1 AS
SELECT p.uniid, p.name
FROM  (
   SELECT student
   FROM   enrolments
   GROUP  BY 1
   HAVING count(*) > 4
   ) e
JOIN   people p ON p.id = e.student;

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0