Select entries that appear more than 4 times

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:

Table enrolment:

id student
462583 1010093
464457 1010093
469823 1010093
471345 1010093
473239 1010093
475371 1010093
477419 1010093
479797 1010093
572312 1010138
577147 1010138
578866 1010138
580596 1010138
582497 1010138

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

id uniid name
10000019 8758024 Emery Schubert
10000021 9808692 Ann Moore
10000025 9833783 Zhen-Tian Chang
10000026 7610575 John Carrick
10000035 9837669 Pamela Mort
10000037 9049091 Sami Korell
10000049 9869271 Mengistu Amberber
10000051 9375982 Colin Fong
10000053 9146607 Dianne Montgomerie
10000073 9804805 Grant Walter
1010093 2220747 Barbara Fremder
1010138 2240781 Say-Kit Ezergailis
1011114 2119574 Evangelos McDonald
1011293 2291530 Grace Hoekstra
1011474 2261154 Chee Jairaj

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:

uniid name
3100280 Mia Wiech
3225571 Cora Prochaska
3335780 Vinh Ha
3255146 Moyang Liu Hongtao
3365147 Frances Ellers
3327487 Keerati Meechowna
3397549 Shane Dinham
3372084 Benjamin Tenenbaum
3252837 Kayserline McFarlane
3350110 Jose Varas
3258061 Alison Lettich
3345581 Snehal Sethu

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.

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;

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

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

Leave a Reply