GROUP BY and COUNT in PostgreSQL

user_1359067 0
Viewed 135169

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?

aggregate-functions count distinct postgresql sql
5 Answers

For followers, I like the OP's inner query method:

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;

Since then you can use HAVING in there as well:

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 HAVING count(*) > 1
) as x;

Or the equivalent CTE

with posts_coalesced as (
     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 )

select count(*) from posts_coalesced;
user_324530
Related