Let's say I have the example below in the picture, with the intended result below the tables

So far, I am able to get the rental count with

SELECT inventory.film_id,
SUM((SELECT COUNT(*) FROM rental WHERE rental.inventory_id = inventory.inventory_id)) AS rentals,
(SELECT title FROM film WHERE film.film_id = inventory.film_id) AS title
FROM inventory
GROUP BY inventory.film_id
ORDER BY inventory.film_id

Result

film_id   rentals    title
1         23         Academy Dinosaur
2          7         Ace Goldfinger
.         ..         ................

I just cannot seem to figure out how to get lets say, category.name from linking film_id to film_category then to category. I have tried adding this code

INNER JOIN film_category ON inventory.film_id = film_category.film_id

but it just returns the same result, it doesn't join it. This wouldn't grab category.name anyways like I am needing.

Any help in understanding what the logic is behind this example would be awesome. thanks

CodePudding user response：

You can write simple query using JOIN

SELECT inventory.film_id, film.title, COUNT(*) AS rentals
FROM inventory
JOIN rental ON rental.inventory_id = inventory.inventory_id
JOIN film ON film.film_id = inventory.film_id
GROUP BY inventory.film_id, film.title
ORDER BY inventory.film_id;

PostgreSQL live example

If you need to get film categories use next version:

SELECT 
    inventory.film_id, 
    film.title, 
    COUNT(DISTINCT rental_id) AS rentals, 
    ARRAY_AGG(DISTINCT category.title)
FROM inventory
JOIN rental ON rental.inventory_id = inventory.inventory_id
JOIN film ON film.film_id = inventory.film_id
JOIN film_category ON film_category.film_id = inventory.film_id
JOIN category ON film_category.category_id = category.category_id
GROUP BY inventory.film_id, film.title
ORDER BY inventory.film_id;

fiddle here

CodePudding user response：

Be careful here. A film can have many rentals and belong to many categories. So you want to join the number of rentals and the list of categories. This means, you should first aggregate your data and then join:

select f.film_id, f.title, c.categories, r.number_of_rentals
from film f
left join
(
  select fc.film_id, string_agg(c.name, ', ' order by c.name) as categories
  from film_category fc
  join category c on c.category_id = fc.category_id
  group by fc.film_id
) c on c.film_id = f.film_id
left join
(
  select i.film_id, count(*) as number_of_rentals
  from rental r
  join inventory in on i.inventory_id = r.inventory_id
  group by i.film_id
) r on r.film_id = f.film_id
order by f.film_id;