Insert aggreated data from one SQL table to another (Postgres)

I have two SQL tables. This is Votes:

and this is votes_aggregate:

As you can see each entry in the votes table is a unique vote. I want to aggregate all the individual votes mapping to the same (catalog_item_id, listing_id) to a unique entry in the aggregate_votes table (2nd table). My Postgres skills are pretty weak so I’m struggling to solve this.

Here is what I have tried so far (it is obviously poor syntax but I think it can help maybe give a clearer idea of what I am trying to achieve):

INSERT INTO votes_aggregate (catalog_item_id, listing_id, yes, no)
FROM 
    (SELECT DISTINCT catalog_item_id, listing_id, 0, 0 
     FROM votes) AS Uniques

UPDATE SET yes = (SELECT COUNT(*) 
                  FROM votes 
                  WHERE catalog_item_id = Uniques.catalog_item_id 
                    AND listing_id = Uniques.listing_id 
                    AND vote_result = 'y'),
           no = (SELECT COUNT(*) 
                 FROM votes 
                 WHERE catalog_item_id = Uniques.catalog_item_id 
                   AND listing_id = Uniques.listing_id 
                   AND vote_result = 'n');

Any tips appreciated!

Answer

You can use FILTER to aggregate only some of the rows.

INSERT INTO votes_aggregate (catalog_item_id, listing_id, yes, no)
SELECT 
    catalog_item_id, 
    listing_id, 
    COUNT(*) FILTER (WHERE vote_result = 'y') as yes,
    COUNT(*) FILTER (WHERE vote_result = 'n') as no
FROM votes
GROUP BY catalog_item_id, listing_id