SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
SELECT c.name AS category, ROUND(COALESCE(SUM(p.amount), 0) / NULLIF(t.total_rental_amount, 0), 6) AS relative_rental_amount, ROUND(COUNT(DISTINCT f.film_id) / NULLIF(t.total_film_count, 0), 4) AS relative_rentals_count FROM category c JOIN film_category fc ON c.category_id = fc.category_id JOIN film f ON fc.film_id = f.film_id LEFT JOIN inventory i ON f.film_id = i.film_id LEFT JOIN rental r ON i.inventory_id = r.inventory_id LEFT JOIN payment p ON r.rental_id = p.rental_id CROSS JOIN ( SELECT SUM(p.amount) AS total_rental_amount, COUNT(DISTINCT f.film_id) AS total_film_count FROM film f LEFT JOIN inventory i ON f.film_id = i.film_id LEFT JOIN rental r ON i.inventory_id = r.inventory_id LEFT JOIN payment p ON r.rental_id = p.rental_id ) AS t GROUP BY c.name, total_rental_amount, total_film_count ORDER BY relative_rental_amount DESC;

Stuck with a problem? Got Error? Ask ChatGPT!

Copy Clear