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.

Paul Maxwell · Accepted Answer · 2018-09-20 05:30:05Z

5

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;

answered Sep 20, 2018 at 5:30

Paul Maxwell

35.7k4 gold badges39 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

fyardlest Over a year ago

Got it my friend! Cristal clear. I will use your recommendation. Thanks a lot!

Laurenz Albe · Accepted Answer · 2018-09-19 19:13:03Z

2

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.

answered Sep 19, 2018 at 19:13

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Collectives™ on Stack Overflow

WITH CTE query using postgresql

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related