MySQL query to return most proposed books in a month

Question

Hello guys,

I have the following MySQL table named "proposals":

• proposal_id
• proposal_user (int
• proposal_book (int)
• proposal_date (Y-m-d)

Users are going to propose books each month so there will be like 50-100 books per month. I would like to know if there's a way of writing a query that can return the most proposed books for a given month.

Thanks in advance.

Answer 1

SELECT  proposal_book, COUNT(*) AS cnt
FROM    proposals
WHERE   proposal_date >= $first_day_of_month
        AND proposal_date < $first_day_of_month + INTERVAL 1 MONTH
GROUP BY
        proposal_book
ORDER BY
        cnt DESC
LIMIT 10

Answer 2

SELECT * FROM proposals GROUP BY proposal_book ORDER BY COUNT(proposal_id) DESC

Answer 3

SELECT proposal_book, COUNT(*)
FROM proposals
WHERE MONTH( proposal_date ) = $given_month AND YEAR( proposal_date ) = $given_year
GROUP BY proposal_book
ORDER BY 2 DESC
LIMIT 0, $desired_number_of_recommendations

You can optimize the where clause by having PHP to calculate the first and last dates of the month, and use </> or BETWEEN clause.