Simple SQL question about getting rows and associated counts

Question

Hi guys, this oughta be an easy one.

My question is very similar to this one; basically, I've got a table of posts, a table of comments with a foreign key for the post_id, and a table of votes with a foreign key for the post id. I'd like to do a single query and get back a result set containing one row per post, along with the count of associated comments and votes.

From the question I've linked to above, it seems that for getting a table back containing just a row for each post and a comment count, this is the right approach:

SELECT a.ID, a.Title, COUNT(c.ID) AS NumComments
FROM Articles a
LEFT JOIN Comments c ON c.ParentID = a.ID
GROUP BY a.ID, a.Title

I thought adding vote count would be as easy as adding another left join, as in

SELECT a.ID, a.Title, COUNT(c.ID) AS NumComments, COUNT(v.id AS NumVotes)
FROM Articles a
LEFT JOIN Comments c ON c.ParentID = a.ID
LEFT JOIN Votes v ON v.ParentID = a.ID
GROUP BY a.ID, a.Title

but I'm getting bad numbers back. What am I missing?

Answer 1

SELECT 
  a.ID, 
  a.Title, 
  COUNT(DISTINCT c.ID) AS NumComments, 
  COUNT(DISTINCT v.id) AS NumVotes
FROM 
  Articles           a
  LEFT JOIN Comments c ON c.ParentID = a.ID
  LEFT JOIN Votes    v ON v.ParentID = a.ID
GROUP BY 
  a.ID, 
  a.Title

Answer 2

try: COUNT(DISTINCT c.ID) AS NumComments

Answer 3

You are thinking in trees, not recordsets.

In the recordset the you get each Comment and each Vote returned multiple times combined with each other. Run the query without the group by and the count to see what I mean.

The solution is simple: use COUNT(DISCTINCT c.ID) and COUNT(DISTINCT v.ID)

Answer 4

SELECT  id, title,
        (
        SELECT  COUNT(*)
        FROM    comments c
        WHERE   c.ParentID = a.ID
        ) AS NumComments,
        (
        SELECT  COUNT(*)
        FROM    votes v
        WHERE   v.ParentID = a.ID
        ) AS NumVotes
FROM    articles a