I am making an attempt to work out the SQL to listing the highest 5 leases for every month on MySQL or SQL Server. The database is the Sakila Pattern database and the desk is rental. Listed below are the primary a number of rows in my database consumer (Navicat Premium):
I would just like the take the month from the rental_date subject.
Every movie has a singular inventory_id.
Up to now I’ve received this:
SELECT MONTH( rental_date ) AS month, inventory_id, COUNT ( inventory_id ) AS no_of_rentals FROM rental GROUP BY MONTH ( rental_date ), inventory_id HAVING COUNT ( inventory_id ) > 1 ORDER BY MONTH ( rental_date ), inventory_id;
Sadly, that returns all movies with multiple rental monthly, which isn’t what I would like.
I would prefer to see one thing extra like:
month inventory_id no_of_rentals _________________________________ April 367 5 April 98 4 (three extra rows) Might 144 5 Might 32 5 (three extra rows) and so forth...
Bonus factors if you will get the movie title!