View SALES_BY_FILM_CATEGORY

This view provides a list of total sales, broken down by individual film category. Because a film can be listed in multiple categories, it is not advisable to calculate aggregate sales by totalling the rows of this view. The view incorporates data from the CATEGORY, PAYMENT, RENTAL, INVENTORY, FILM, FILM_CATEGORY, and CATEGORY tables.

Query

SQL

SELECT
  c.name AS category, 
  SUM(p.amount) AS total_sales
FROM 
  payment AS p
INNER JOIN 
  rental AS r ON p.rental_id = r.rental_id
INNER JOIN 
  inventory AS i ON r.inventory_id = i.inventory_id
INNER JOIN 
  film AS f ON i.film_id = f.film_id
INNER JOIN 
  film_category AS fc ON f.film_id = fc.film_id
INNER JOIN 
  category AS c ON fc.category_id = c.category_id
GROUP BY 
  c.name
ORDER BY 
  total_sales DESC

Columns

This view contains 2 columns.

CATEGORY: VARCHAR(25)
TOTAL_SALES: DECIMAL