CODEWITHSUNDEEP
sqlpostgresqljoincount
Pablo Fernandez
Pablo Fernandez

Reputation: 287390

How to get counts from various joins to the same table in SQL?

I have two tables, blogs and posts, with the expected relationship between them. Posts can be published or draft. I want to select all blogs with the counts of published and draft posts. How can I do it?

I tried this query:

SELECT blogs.*,
       COUNT(published_posts.*) AS published_post_count,
       COUNT(draft_posts.*) AS draft_post_count
FROM blogs
JOIN posts AS published_posts ON published_posts.blog_id=blogs.id AND 
                                 published_posts.state = "published"
JOIN posts AS draft_posts ON draft_posts.blog_id=blogs.id AND 
                             draft_posts.state = "draft"
GROUP BY blogs.id

but I end up with the same number for both published_post_count and draft_post_count and being wrong for both.

Then I got desperate and trie:

SELECT blogs.*,
       COUNT(published_posts.*) AS published_post_count,
       COUNT(draft_posts.*) AS draft_post_count
FROM blogs
JOIN (SELECT * FROM posts WHERE posts.state="published") AS published_posts
     ON published_posts.blog_id=blogs.id 
JOIN (SELECT * FROM posts WHERE posts.state="draft") AS draft_posts
     ON draft_posts.blog_id=blogs.id
GROUP BY blogs.id

but in both cases I had the same wrong results.

What's the proper way to do this?

Thanks.

Upvotes: 2

Views: 2097

Answers (3)

Nikola Markovinović
Nikola Markovinović

Reputation: 19346

You might count different states and join back to blogs:

SELECT blogs.*, p.published_post_count, p.draft_post_count
FROM blogs
JOIN 
(
  SELECT posts.blog_id,
         SUM(case when posts.state = 'published' then 1 end) 
             AS published_post_count,
         SUM(case when posts.state = 'draft' then 1 end) 
             AS draft_post_count
    FROM posts
   GROUP BY blog_id
) p
ON p.blog_id=blogs.id

Upvotes: 2

David Rasuli
David Rasuli

Reputation: 832

Try : 

GROUP BY blogs.id,published_posts.[theKeyOfThisTable],draft_posts.[theKeyOfThisTable]

as you should contain the columns of the "select" at your "group by" clause

Upvotes: 1

Arion
Arion

Reputation: 31239

If you are just interested in the COUNT and you are also JOINing the tables. So there has to be one or more post that has been published and one or more that is draft. So maybe this will help you:

SELECT blogs.*,
       (
            SELECT COUNT(*)
            FROM posts
            WHERE posts.blog_id=blogs.id
            AND posts.state = "published"
       ) AS published_post_count,
       (
            SELECT COUNT(*)
            FROM posts
            WHERE posts.blog_id=blogs.id
            AND posts.state = "draft"
       ) AS draft_post_count,
FROM blogs

Upvotes: 4

Related Questions