title: WITH CTE query using postgresql
body: <p>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?</p>
  
  <p>I create this query and I have the total of each film title (Sakila database):</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>I would like to do the same using the WITH (CTE) and for that I create this code :</p>
  
  <pre><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;
  </code></pre>
  
  <p>The problem is that the result did not show the total of each film title, but instead every film title separately.</p>