skinkelynet skinkelynet - 2 months ago 7
SQL Question

GROUP BY and COUNT in PostgreSQL

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

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.

Comments