Clever Geek Handbook

SQL Query: How to Perform Multiple Counts (Post Count returned and Topic Count for Each Forum)

I am having trouble getting the number of posts and topics for each of the forums in the database. I can get these values ​​by doing 2 requests, but I'm wondering if it is possible to do this with only one request.

This request gets the number of topics for the forum:

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

This request gets the number of forum posts:

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 columns and topics in one query?

+2


source to share


2 answers 


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


+3


source 


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 when you want to count forums with zero topics or posts

0


source






More articles:


All Articles