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|
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.
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