Search code examples
sqlgroup-bycountmariadbhaving-clause

Show the foreign key id's that occur more than n times

[mariaDB]

Hi, I need to show the foreign key ids from another table that occur more than 4 times in my primary table.

for example: 5 persons have a foreign key id from my secondary table of 001, 6 persons have a foreign key id of 002, and 3 persons have a foreign key id of 003.

Now I need to output the foreign key ID's that have more than 4 persons, in this example that would be 001 and 002.

Thanks and sorry if unclear, kinda hard to explain.

Solution

  • You seem to be looking for aggregation. Assuming that your foreign key column is called fk, you would do:

    select fk, count(*) no_persons
from mytable
group by fk
having count(*) > 4