How can I combine these two SQL queries into one query?

Question

I am using the Sakila database in MySql 5.6.25.

The first request gives me films that provide our "store" with the highest income in descending order. I also show the rental time and 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 shows us how many copies of the movie we have:

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 work ... independently ... but when I try to combine them, they give unexpected results. Please show me the correct way to combine them without changing the way the results are displayed.

Answer 1

, , mysql sakila, .

( - )

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

InvCount = 8 ( BUCKET BROTHERHOOD).

count(i.film_id) as InvCount

, alias i rental r .

, BUCKET BROTHERHOOD 34 8 .

, InvCount = 34. : 8.

, .

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

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