CODEWITHSUNDEEP
mysqljoingroup-byinner-join
Ebony Maw
Ebony Maw

Reputation: 524

How can I combine these two SQL queries into a single query?

I am using the Sakila database within MySql 5.6.25.

The first query gives me the films which are providing our "store" with the highest revenue in descending order. I also show the times it has been rented and it's rental rate:

SELECT f.title, f.rental_rate, count(r.rental_id) AS "Times Rented", count(r.rental_id) * f.rental_rate as Revenue
    from film f 
    INNER JOIN inventory i
       ON f.film_id = i.film_id
    INNER JOIN rental r
        ON r.inventory_id = i.inventory_id
    GROUP BY f.title
    ORDER BY revenue DESC

The second query is showing us how many copies of the film we have on hand:

SELECT film.title, count(inventory.film_id)
from film
INNER JOIN inventory
ON film.film_id = inventory.film_id
group by film.title

I understand how both queries are working... independently... but when I try to combine them, they produce unexpected results. Please show me the correct way to combine them without changing the manner in which results are being shown.

Upvotes: 4

Views: 292

Answers (1)

Drew
Drew

Reputation: 24959

For those unaware, mysql comes with a great practice database call sakila on which to practice queries.

correct approach (or something like it)

SELECT f.title, 
f.rental_rate, 
count(r.rental_id) AS "Times Rented", 
count(r.rental_id) * f.rental_rate as Revenue,
(select count(*) from inventory where film_id=f.film_id) as InvCount
    from film f 
    INNER JOIN inventory i
       ON f.film_id = i.film_id
    INNER JOIN rental r
        ON r.inventory_id = i.inventory_id
    GROUP BY f.title
    ORDER BY revenue DESC

This will give you InvCount=8 for first row (film BUCKET BROTHERHOOD).

wrong approach

count(i.film_id) as InvCount

is because alias i is driven by its last join on alias rental r in the whole query.

So for the first row of output, BUCKET BROTHERHOOD, with 34 rentals and 8 actual inventory of the item ....

If you do it the wrong way it shows InvCount=34. The correct answer is 8.

Always do a sanity check with the data to keep the boss from yelling at you.

select film_id,title from film where title like 'bucket br%'; -- film_id=103

select count(*) from inventory where film_id=103; -- count=8

Upvotes: 2

Related Questions