I have a Posts table and PostComments table of a blog system. I want to count and sort the posts by comment count but my query won't work.:
SELECT Posts.PostID, Posts.DateCreated, Posts.Title, Posts.Description,
Posts.Hits, (SELECT Count(CommentID) FROM PostComments WHERE
PostComments.PostID=Posts.PostID AND PostComments.IsApproved=True) AS
CommentCount FROM Posts ORDER BY Posts.PostID DESC;
I also tried:
SELECT Posts.PostID, Posts.DateCreated, Posts.Title, Posts.Description,
Posts.Hits, Count([CommentID]) AS CommentCount FROM Posts INNER JOIN PostComments
ON Posts.PostID = PostComments.PostID;
But have error "You tried to execute a query that does not include specified expression 'PostID' as a part of an aggregate function."