SELECT/COUNT statement works alone but doesn't work in the context of the table

Question

I'm a student using sakila dvd rental database to do a project, using PostgreSQL 15.2 and pgAdmin4. After some other work, I have this code populating a table that is going to compare rental duration and frequency among categories:

INSERT INTO summary (store_id, cat_group, total_rentals, total_titles, avg_rental_duration) 
    SELECT DISTINCT detailed.store_id, cat_group_fx(detailed.category_name), 
        (SELECT COUNT (detailed.rental_id)
             FROM detailed, summary AS selfsum
            WHERE selfsum.store_id = detailed.store_id
             AND cat_group_fx(detailed.category_name) = selfsum.cat_group
            GROUP BY detailed.store_id, selfsum.cat_group),  
        (SELECT COUNT (inventory.inventory_id)
             FROM inventory, detailed, summary AS selfsum
            WHERE inventory.film_id = detailed.film_id
             AND inventory.store_id = detailed.store_id
            AND detailed.store_id = selfsum.store_id
             AND cat_group_fx(detailed.category_name) = selfsum.cat_group
            GROUP BY detailed.store_id, selfsum.cat_group), 
        AVG(rental.return_date - rental.rental_date) 
    FROM detailed, rental 
    GROUP BY detailed.store_id, detailed.category_name;

When I run the entire code (some 60 lines) I get entire columns full of [null] for total_rentals and total_titles in my summary table. example

But when I highlight and run each individual SELECT COUNT statement by itself, it miraculously counts and the counts appear correct, images below. total_rentals count example total_titles count example

Why is this happening & how can I get the counts into the main table?

More info edit: cat_group_fx is a user function that sorts the rentals into categories by genre, using IN to compare and CASE to sort. "detailed" is another table much farther up the code that simply gathers data from the various Sakila tables, including rental_id, film_id, store_id, category.name, rental_date and return_date. The point of the "detailed" table is to gather and coordinate the relevant granular data that is then transformed into the "summary" table shown here using aggregates and user functions. It is part of the assignment.

Answer 1

Each “column” in a select statement needs to return a single value per row in the resultset. If the column is calculated then that calculation needs to return a single value.

If the calculation involves another select statement (rather than, for example, a simple addition) then that is a subquery.

The subquery is “correlated” if it is linked back to the main query e.g.

SELECT T1.ROW_ID, T1.NAME
,(SELECT COUNT(1) 
  FROM TABLE2 T2 
  WHERE T2.PARENT_ID = T1.ROW_ID
) COUNT_T2
FROM TABLE1 T1

The subquery is correlated back to the main query using WHERE T2.PARENT_ID = T1.ROW_ID. So for each row in T1, the count of matching records in T2 is calculated.

You can have non-correlated subqueries e.g.

SELECT T1.ROW_ID, T1.NAME
,(SELECT COUNT(1) 
  FROM TABLE2 T2 
) COUNT_T2
FROM TABLE1 T1

In this case, every record in the resultset will have the same value in the COUNT_T2