Postgres Error: must appear in the GROUP BY clause or be used in an aggregate function I'm a novice when it comes to Postgres, and I realize similar questions have been asked before, however, I'm struggling to make this query work:

SELECT    p_search.id, count(kia.image_id) AS count
FROM      (SELECT id,
                  keyword,
                  to_tsvector('english',keyword) AS tskw
          FROM keyword) AS p_search
LEFT JOIN keyword_image_assoc AS kia 
ON        kia.keyword_id = p_search.id
WHERE     p_search.tskw @@ plainto_tsquery('english','resident life')
OR        p_search.tskw @@ plainto_tsquery('english','wofford')
GROUP BY  kia.image_id
ORDER BY  count desc


For context, I have three tables:


image which has an id as the primary key
keyword which has id as the primary key and keyword which is a string representing a keyword
keyword_image_assoc which has an id, image_id and keyword_id which is used to associate images and keywords canonically.


If I add p_search.id to the GROUP BY clause, the results aren't correct - count is always 1. Is there a way to group the results so the image_ids have the count of the number of matching keywords?

I'm looking for two columns count - the number of matching keywords and image_id the id of the corresponding image. Thanks!
Have a look at Postgres docs about GROUP BY:


  When GROUP BY is present, or any aggregate functions are present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions or when the ungrouped column is functionally dependent on the grouped columns, since there would otherwise be more than one possible value to return for an ungrouped column. A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column.


According to this you cannot reference a field on the SELECT statement if it doesn't appear on the GROUP BY clause or without using an aggregated function.

In your example, you're referencing p_search.id but it doesn't appear on the GROUP BY clause. You could try to change it by kia.image_id