'postgresql selecting 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'm okay with making the view and getting the columns I need but the syntax is so confusing and the course I'm doing is very unclear. 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 enrollment table more than 4 times? Sorry if the question is a bit bad I'm extremely sleep deprived and confused.
edit: here are some example tables and what I tried to do-
enrolment table relevant info:
| 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 |
so students 1010093 and 1010138 would fit the criteria because they appear more than 4 times, but there are many entries that do not appear more than 4 times.
people table relevant info:
(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;
here's a sample of the 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 |
This was the expected output in full:
| uniid | name |
|---|---|
| 3001394 | Jeffrey Caldwell |
| 3087372 | Philip Lun |
| 3108948 | Sugianto Arsie |
| 3122927 | Seaton Warburton |
| 3132303 | Amy Berg |
| 3134147 | Hannah Kola |
| 3160479 | Ksenia Mardakhaeva |
| 3163349 | Kerry Plant |
| 3173796 | Adam Rositano |
| 3187169 | Giles Erol |
| 3207313 | Livio Tjia |
| 3209530 | Su Song |
| 3229297 | Yoke Anthoney |
| 3245227 | Pollyanna Risk |
| 3272803 | Jesus Ferrer |
| 3294743 | Deviani Hongganata |
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
