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.
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