SQL window function and joins

Question

My query is as follows:

SELECT 
    category_name, film_title, rental_count
FROM
    (SELECT 
        c.name AS category_name, f.title AS film_title, 
        COUNT(r.rental_date) OVER (PARTITION BY f.title) AS rental_count
    FROM 
        category c
    JOIN 
        film_category fc ON fc.category_id = c.category_id
    JOIN 
        film f ON fc.film_id = f.film_id
    JOIN 
        inventory i ON i.film_id = f.film_id
    JOIN 
        rental r ON i.inventory_id = r.inventory_id) t1
WHERE 
    category_name IN ('Animation', 'Children', 'Classics', 'Comedy', 'Family', 'Music')
ORDER BY 
    1, 2

And the results are as follows:

As you can see that the title of the movie is repeated, as you already know, it shouldn't. However, I can't understand why it is happening?

The answer should actually be as the picture above.

Any help is highly appreciated.

question from:https://stackoverflow.com/questions/65867586/sql-window-function-and-joins

Answer 1

Why is it happening? That is what window functions do. They don't reduce the number of rows. That add new columns on existing rows (typically with data gathered from multiple rows).

You simply want an aggregation:

SELECT c.name AS category_name, f.title AS film_title, 
       COUNT(r.rental_date) as rental_count
FROM category c JOIN
     film_category fc
     ON fc.category_id = c.category_id JOIN
     film f
     ON fc.film_id = f.film_id JOIN
     inventory i
     ON i.film_id = f.film_id JOIN
     rental r
     ON i.inventory_id = r.inventory_id
WHERE c.category_name IN ('Animation', 'Children', 'Classics', 'Comedy', 'Family', 'Music')
GROUP BY 1, 2