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:

    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


  • 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;
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2