PostgreSQL clause - GROUP BY

Question

I want to search by tags, and then list all the articles with this tag, as well as the number of matching tags. So, for example, I could:

 Page1 - 2 (has css and php tag)
 Page2 - 1 (has only css tag)

Query:

SELECT COUNT(t.tag)
FROM a_tags t
JOIN w_articles2tag a2t ON a2t.tag = t.id 
JOIN w_article a ON a.id = a2t.article 
WHERE t.tag = 'css' OR t.tag = 'php'
GROUP BY t.tag
LIMIT 9

When I just set COUNT(t.tag), the query works, and I get good results. But if I add, for example, IDmy article, I get the following error:

ERROR: the column "a.title" should appear in the GROUP BY clause or be used in the LINE 1 aggregate function: SELECT COUNT (t.tag), a.title FROM a_tags t

How to add the specified columns to this query?

Answer 1

-, , Postgres 9.1 ( 9.1)...

GROUP BY , GROUP BY (Peter Eisentraut)

:
Rails PostgreSQL

@Michael answer . , , . GROUP BY w_article.id, a_tags.id.

, ,

:

SELECT COUNT(t.tag) AS ct, a.* -- any column from a allowed ...
FROM   a_tags         t
JOIN   w_articles2tag a2t ON a2t.tag = t.id 
JOIN   w_article      a   ON a.id = a2t.article 
WHERE  t.tag IN ('css', 'php')
GROUP  BY a.id           -- ... since grouped by pk column of a
LIMIT  9

, id w_article.
, , :

SELECT a.*, ct
FROM  (
   SELECT a2t.article AS id, COUNT(*) AS ct
   FROM   a_tags         t
   JOIN   w_articles2tag a2t ON a2t.tag = t.id 
   GROUP  BY a.article 
   LIMIT  9      -- LIMIT early - cheaper
   ) sub
JOIN   w_article a USING (id);  -- attached alias to article in the sub

:
?

: - , id . article_id .. . , .

Answer 2

GROUP BY, , . GROUP BY "min (a.title)".

SELECT COUNT(t.tag), a.title FROM a_tags t
JOIN w_articles2tag a2t ON a2t.tag = t.id 
JOIN w_article a ON a.id = a2t.article 
WHERE t.tag = 'css' OR t.tag = 'php' GROUP BY t.tag, a.title LIMIT 9