Selecting rows where there are at least X rows with some values in a column via sqlite3?

Question

Say I have the following data:

|bookID|data|
|1|a|
|1|b|
|1|c|
|2|x|
|2|y|
|3|t|

I want to select all the books that have at least X rows. So if X=3 then I'd only get all the rows with bookID=1. If X=2 then I'd only get all the rows with bookID=1 or bookID=2, etc.

Can I do this with SQLite3?

Answer 1

This example will get all books that have 3 or more rows.

  SELECT * FROM Books WHERE BookID IN
     (SELECT BookID FROM Books GROUP BY BookID HAVING COUNT(*) >= 3)

Answer 2

Here is a way to do it by JOINing a subquery:

SELECT bookID FROM books
    JOIN (SELECT COUNT(*) AS book_count,bookID FROM books GROUP BY bookID)
        USING (bookID)
    WHERE book_count >= 2
    GROUP BY bookID

Larry Lustig’s answer is much less verbose (and probably faster, too).