All we need is an easy explanation of the problem, so here it is.
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:
Students 1010093 and 1010138 would fit the criteria because they appear more than 4 times. But there are many students with fewer entries.
(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;
|3255146||Moyang Liu Hongtao|
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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;
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂