user2789945 user2789945 - 1 month ago 19
MySQL Question

Minimizing redundancy of MySQL query

I'm having a bit of trouble trying to reduce the redundancy of a query in MySQL. I currently have it working, but it feels like I have too much overhead because it uses a redundant subquery. What I am trying to do is use a dvd rental database to find which store location has rented out more dvd's for each month in 2005.

Here is the working query

SELECT b.month, c.store_id, b.maxRentals
FROM
(SELECT a.month, MAX(a.rentalCount) as maxRentals
FROM
(SELECT MONTH(rental.rental_date) as month, inventory.store_id, count(1) as rentalCount
FROM rental

INNER JOIN inventory
ON rental.inventory_id = inventory.inventory_id

WHERE YEAR(rental.rental_date) = 2005

GROUP BY MONTH(rental.rental_date), inventory.store_id
) a
GROUP BY a.month
) b

INNER JOIN

(SELECT MONTH(rental.rental_date) as month, inventory.store_id, count(1) as rentalCount
FROM rental

INNER JOIN inventory
ON rental.inventory_id = inventory.inventory_id

WHERE YEAR(rental.rental_date) = 2005

GROUP BY MONTH(rental.rental_date), inventory.store_id
) c
ON b.maxRentals = c.rentalCount

GROUP BY b.month;


Notice how the subquery with the alias of "c" is the exact same subquery of alias "a". I'm not sure if there's a way to get rid of this, as I can't inner join on an alias. Am I just stuck with a giant query, or is there something else I can do?

Answer

I am 90% certain this query will achieve your intentions:

SELECT MONTH(r.rental_date), i.store_id, COUNT(*)
FROM rental r
LEFT JOIN inventory i ON r.inventory_id = i.inventory_id
WHERE YEAR(r.rental_date) = 2005
GROUP BY MONTH(r.rental_date), i.store_id

Let me know how it goes!

Edit: to answer the question which store location has rented out more dvd's for each month in 2005:

SELECT x.rental_month, x.store_id, MAX(x.rental_count) FROM (
SELECT MONTH(r.rental_date) AS rental_month, i.store_id AS store_id, COUNT(*) AS rental_count
FROM rental r LEFT JOIN inventory i ON r.inventory_id = i.inventory_id
WHERE YEAR(r.rental_date) = 2005
GROUP BY MONTH(r.rental_date), i.store_id) x
GROUP BY x.rental_month, x.store_id

I was explicit by using aliases everywhere, you could probably omit some. Hopefully this helps...

Edit: Dirty hack:

SELECT x.rental_month, x.store_id, MAX(x.rental_count) FROM (
SELECT MONTH(r.rental_date) AS rental_month, i.store_id AS store_id, COUNT(*) AS rental_count
FROM rental r LEFT JOIN inventory i ON r.inventory_id = i.inventory_id
WHERE YEAR(r.rental_date) = 2005
GROUP BY MONTH(r.rental_date), i.store_id
ORDER BY MONTH(r.rental_date) ASC, COUNT(*) DESC) x
GROUP BY x.rental_month

Ref: http://kristiannielsen.livejournal.com/6745.html

But then does this satisfy you, seeing as you do already have a working query...

Comments