How do I prevent duplicate rows in Postgresql while using COUNT and partitions?

first time question asker here and pretty new to SQL. I’m doing a project in postgresql and pgadmin 4 (if that matters) and I’m getting duplicates in my ‘title’ and ‘title_mo_rentals’ fields when using COUNT and inserting. Here is my table:

CREATE TABLE detailed_report (  
  rental_id INT PRIMARY KEY
, store_num INT
, rental_year INT
, rental_month VARCHAR(9)
, genre VARCHAR(25)
, genre_mo_rentals INT
, title VARCHAR (50)
, title_mo_rentals INT
, genre_ytd_rentals INT  
);

Here is my code so far:

INSERT INTO detailed_report (
  rental_id
, store_num
, rental_year
, rental_month
, genre
, genre_mo_rentals
, title
, title_mo_rentals
, genre_ytd_rentals
)
SELECT
  r.rental_id
, i.store_id AS store_num
, DATE_PART('year', r.rental_date) AS rental_year
, TO_CHAR(r.rental_date, 'Month') AS rental_month
, cat.name AS genre
, COUNT(r.rental_id) OVER (PARTITION by i.store_id, DATE_PART('year', r.rental_date),  TO_CHAR(r.rental_date, 'YYYY/MM'), cat.name) as genre_mo_rentals
, f.title AS title
, COUNT(r.rental_id) OVER (PARTITION by i.store_id, DATE_PART('year', r.rental_date), TO_CHAR(r.rental_date, 'YYYY/MM'), cat.name, f.title) AS title_mo_rentals
, COUNT(r.rental_id) OVER (PARTITION by i.store_id, DATE_PART('year', r.rental_date), cat.name) AS genre_ytd_rentals
FROM rental r
INNER JOIN inventory i ON r.inventory_id = i.inventory_id
INNER JOIN film f ON i.film_id = f.film_id
INNER JOIN film_category fc ON f.film_id = fc.film_id
INNER JOIN category cat ON fc.category_id = cat.category_id
;

I’m OK with the duplicates in the other fields, but I would like ‘title’ and ‘title_mo_rentals’ to only display one row instead and can’t seem to find a way to make it happen.

enter image description here

So I’ve obviously tried using partitions and I’ve experimented with CTEs but I can’t seem to make those work either. I’ve been scouring the internet for days and trying/failing everything I find. Any help would be greatly appreciated!

You need to sign in to view this answers