What is the mysql function to show 5 most frequent records in a specific time frame? Using 3 tables-CodePudding

My database tables and fields:

table author

AuthorID pk
AuthorFirstName 
AuthorLastName
AuthorNationality

table book

bookid pk
booktitle
bookauthor fk from author.authorid
genre

table borrower

borrowid
clientid pk
bookid fk from book.bookid
borrowdate

table client

clientid fk from borrower.clientid
clientfirstname
clientlastname
clientdob
occupation

I am trying to get the First and Last names of the top 5 authors from the books that clients most borrowed during 2017. So I would think that I would select the author first and last name, inner join the author table with the book table focusing on the author id, and then inner joining the book table with the borrower table, focusing on the bookid which could lead to bookauthor and identify the name of the author. I am not sure if I am wrong in this.

So this is what I tried, to get the top 5 most popular authors among all the borrowed books.

select Author.AuthorFirstName, Author.AuthorLastName, Borrower.Bookid, Count(Borrower.BorrowId) as numberofborrows 
from Author  
inner join Book 
on  Author.AuthorId = Book.BookAuthor 
inner join Borrower
on Book.BookId = Borrower.BookId
where BorrowDate between '2017-01-01' and '2017-12-31'
limit 5

This gives me an error, error code 1140. But I am not sure if this is the correct way.

This is what I tried to get all the author names between the year of 2017, I did not include the code for top 5 authors from the books that were most borrowed:

select Author.AuthorFirstName, Author.AuthorLastName 
from Author inner join Book on  Author.AuthorId = Book.BookAuthor 
inner join Borrower on Book.BookId = Borrower.BookId 
where BorrowDate between '2017-01-01' and '2017-12-31'

This code did work and gave me the names of the Authors from borrows that ocurred during 2017. How do I mix the between function with a function that could display names of the 5 authors that had most borrows from their written books during 2017?

CodePudding user response：

Starting from your working query you of course need to join the book ids to. However in your attempt you simply joined the borrows without any further means; then trying to count the borrow ids, if it did work, would yield 1 (one) for any column anyway. What you instead need to do is grouping the results to appropriate sub-sets:

GROUP BY Author.AuthorFirstName, Author.AuthorLastName, Book.Bookid

Now all books are collected in sub-sets on which you can count the borrower.borrowid.

However these sets might appear in any order and you might get any first five books; limit 5 will only be of use if you sort the results by the count of borrows:

ORDER BY numberofborrows DESC

So the whole query might look as follows (note: untested; if you find a bug, please fix yourself):

select
  Author.AuthorFirstName, Author.AuthorLastName, Book.Bookid,
  Count(Borrower.BorrowId) as numberofborrows 
from Author
  inner join Book on Author.AuthorId = Book.BookAuthor 
  inner join Borrower on Book.BookId = Borrower.BookId
where BorrowDate between '2017-01-01' and '2017-12-31'
GROUP BY Author.AuthorFirstName, Author.AuthorLastName, Book.Bookid
ORDER BY numberofborrows DESC
limit 5