GROUP BY and COUNT in PostgreSQL

Question

The query:

SELECT COUNT(*) as count_all,         posts.id as post_id  FROM posts    INNER JOIN votes ON votes.post_id = posts.id  GROUP BY posts.id; 

Returns n records in Postgresql:

 count_all | post_id -----------+---------  1         | 6  3         | 4  3         | 5  3         | 1  1         | 9  1         | 10 (6 rows) 

I just want to retrieve the number of records returned: 6.

I used a subquery to achieve what I want, but this doesn't seem optimum:

SELECT COUNT(*) FROM (     SELECT COUNT(*) as count_all, posts.id as post_id      FROM posts      INNER JOIN votes ON votes.post_id = posts.id      GROUP BY posts.id ) as x; 

How would I get the number of records in this context right in PostgreSQL?

Answer 1

I think you just need COUNT(DISTINCT post_id) FROM votes.

See "4.2.7. Aggregate Expressions" section in http://www.postgresql.org/docs/current/static/sql-expressions.html.

EDIT: Corrected my careless mistake per Erwin's comment.