'Can I use a SUM function when inner joining several tables?

I'm joining tables to view rental films by genre, but I also want to know how much was rented per genre using the SUM function. The amount of rental is within payment table. Several tables have been joined. So far, the data output shows me the columns I want but the last step is to view by total amount per genre. Here's my query:

This query returns genre, film_id, title and amount

SELECT category.name AS genre,film.film_id,film.title,payment.amount 
FROM payment
INNER JOIN rental
ON payment.rental_id = rental.rental_id
INNER JOIN inventory
ON rental.inventory_id = inventory.inventory_id
INNER JOIN film
ON inventory.film_id = film.film_id
INNER JOIN film_category
ON film.film_id = film_category.film_id
INNER JOIN category
ON film_category.category_id = category.category_id
ORDER BY category.name
sqldatabasegroup-bysuminner-join


Solution 1:[1]

Does this get you what you need?

SELECT 
    category.name AS genre,  
    SUM(payment.amount) 
FROM payment
INNER JOIN rental
    ON payment.rental_id = rental.rental_id
INNER JOIN inventory
    ON rental.inventory_id = inventory.inventory_id
INNER JOIN film
    ON inventory.film_id = film.film_id
INNER JOIN film_category
    ON film.film_id = film_category.film_id
INNER JOIN category
    ON film_category.category_id = category.category_id
ORDER BY category.name
GROUP BY genre

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Paul Neralich

Related Questions