CODEWITHSUNDEEP
mysqlsql
Donatas Vileita
Donatas Vileita

Reputation: 55

Find the most frequently occurring item in table

I am making a library database and I am having a problem in one place. I need to find most popular borrowed book. I have these tables.

--Books--          --BookReader--             --Readers--
book_id              book_id                    reader_id
                 reader_id

FOR EXAMPLE DATA IN TABLE BookReader;

----BookReader---
reader_id book_id
1        |    2
1        |    3
3        |    2

I think I need to count every book_id in BookReader which is equal(2, 3 and so on) and than find that max number.

I can't write the SQL query to find most popular borrowed book(bookId)?

Upvotes: 3

Views: 448

Answers (1)

Ashish Gaur
Ashish Gaur

Reputation: 2050

select book_id,count(book_id) as buys
from BookReader
group by book_id
order by buys desc
limit 1

Here is the SQLFiddle

Upvotes: 1

Related Questions