CODEWITHSUNDEEP
sqlpostgresql
sharmapn
sharmapn

Reputation: 21

Two table join Postgres count child rows

I have two tables: posts and replies.

The post table contains these columns

postid | forumName | title | content

The replies table contains these columns

replyid | content | postid

I would like to have a sql query that joins these tow tables and returns for each Forum

forumName | Total Number of Posts | Total Number of Replies

This is hard as the two tables are linked using postId.

select forum, count(id) as postsNum
from posts
group by forum
order by postsNum desc

Upvotes: 1

Views: 124

Answers (1)

gotqn
gotqn

Reputation: 43656

Are you looking for this:

select p.forum
      ,count(distinct id) as posts
      ,count(r.replyid) as replies
from posts p
inner join replices r
    on p.postid = r.postid
group by p.forum

Upvotes: 2

Related Questions