SQLize | PHPize | SQLtest

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), 5) 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; ; show status like 'Last_query_cost';