Is there any way to get owner ids who have more than 2 books using PostgreSQL query?

Question

In libraries table, there are owner_id and book_id.

Each owner can have only one book. For example,

  id|owner_id|book_id
  3 |  2     | 1
  5 |  2     | 1

But now some owners have more than 2 books.

  id|owner_id|book_id
  9 |  5     | 1
  11|  5     | 2

Is there any way to get owner ids who have more than 2 books using PostgreSQL query?

Answer 1

You can use aggregation and filter with a having clause:

select owner_id from librarires group by owner_id having count(*) > 1

This assumes that (book_id, owner_id) tuples are unique in the table. If duplicate may happen and you want to count only distinct books per owner, then:

select owner_id from librarires group by owner_id having count(distinct book_id) > 1