WITH CTE query using postgresql

Question

I am learning how to use SQL CTE and I would like to compare two query to have the same answer (using postgresql) but I fail can someone help plese?

I create this query and I have the total of each film title (Sakila database):

SELECT COUNT(r.rental_id) rental_count, 
    f.title as "Film"
FROM film f 
JOIN inventory i 
ON f.film_id = i.film_id
JOIN rental r USING (inventory_id) 
GROUP BY f.title
ORDER BY rental_count DESC;

I would like to do the same using the WITH (CTE) and for that I create this code :

WITH table1 AS (           
            SELECT f.film_id,
            f.title as "Film"
            FROM film f),

 table2 AS (           
        SELECT r.inventory_id,
        COUNT(r.rental_id) rental_count,
        i.film_id,
        i.inventory_id
        FROM inventory i
        JOIN rental r USING (inventory_id)
        GROUP BY r.inventory_id, i.film_id, i.inventory_id)

SELECT *
FROM table1
JOIN table2 
ON table1.film_id = table2.film_id;

The problem is that the result did not show the total of each film title, but instead every film title separately.

user_2067753 · Answer

The second CTE would need to be grouped by film to produce an equivalent end result.

WITH table1 AS (
    SELECT
        f.film_id
      , f.title AS "Film"
    FROM film f
    )
, table2 AS (
    SELECT
        COUNT(r.rental_id) rental_count
      , i.film_id
    FROM inventory i
    JOIN rental r ON i.inventory_id = r.inventory
    GROUP BY i.film_id
    )
SELECT
      table2.rental_count
    , table1.Film
FROM table1
JOIN table2 ON table1.film_id = table2.film_id
ORDER BY rental_count DESC;

Just a note; I would not recommend using both natural and non-natural join types in a single query, it can get quite confusing.

SELECT
       COUNT(r.rental_id) rental_count
     , f.title AS "Film"
FROM film f
JOIN inventory i ON f.film_id = i.film_id
JOIN rental r ON i.inventory_id = r.inventory_id -- change here
GROUP BY f.title
ORDER BY rental_count DESC;

user_6464308 · Answer

To get the same result, you'd have to aggregate and group in the second query just like in the first:

WITH table1 AS (...),
     table2 AS (...)
SELECT count(table2.rental_count) AS rental_count,
       table1."Film"
FROM table1
   JOIN table2 USING (film_id)
GROUP BY table1."Film"
ORDER BY rental_count DESC;

Basically you use the CTEs instead of the original tables.

WITH CTE query using postgresql

2 Answers