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.

Schema

This view belongs to schema sakila.

Query

SQL

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

Columns

This view contains 2 columns.

category: character varying(25)
total_sales: numeric