CODEWITHSUNDEEP
sql
Uncle Paulie
Uncle Paulie

Reputation: 21

SQL Query: How to do multiple counts?(return number of posts as well as number of topics for each forum)

I'm having some trouble getting the number of posts and topics for each of the forums on the database. I can get these values doing 2 queries but I wonder if it is possible to do it with only one query.

This query gets the number of topics per forum:

select forums.forumId, forums.forumName, count(*) as Topics FROM Topics 
INNER JOIN forums ON forums.forumId = topics.forumID 
GROUP BY forums.forumId;

This query gets the number of posts per forum:

select forums.forumId, forums.forumName, count(*) as Posts FROM posts 
INNER JOIN topics ON topics.topicID = posts.topicId 
INNER JOIN forums ON forums.forumId = topics.forumID 
GROUP BY forums.forumId;

How do I get both post and topic count in just one query?

Upvotes: 2

Views: 366

Answers (2)

Wilhelm
Wilhelm

Reputation: 6676

SELECT  forums.forumId, forums.forumName,
        COUNT(DISTINCT topics.TopicID) AS Topics,
        COUNT(posts.topicId) as Posts
FROM    forums
LEFT OUTER JOIN topics
ON      topics.forumID = forums.forumId
LEFT OUTER JOIN posts 
ON      posts.topicId  = topics.topicID
GROUP BY
        forums.forumId

You want to use left outer joins if you want to count forums with zero topics or posts

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425593

SELECT  forums.forumId, forums.forumName,
        COUNT(DISTINCT topics.TopicID) AS Topics,
        COUNT(*) as Posts
FROM    forums
INNER JOIN
        topics
ON      topics.forumID = forums.forumId
INNER JOIN
        posts 
ON      posts.topicId  = topics.topicID
GROUP BY
        forums.forumId

Upvotes: 3

Related Questions