views:

28

answers:

3

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."

+1  A: 

Try

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
GROUP BY Posts.PostID, Posts.DateCreated, Posts.Title, Posts.Description, Posts.Hits
ORDER BY Count([CommentID]) DESC
Chris Diver
@Chris Diver: Access asks for CommentCount.
HasanGursoy
Ah okay, never used access before, that's something you can do in SQL Server. Change it to `COUNT([CommentID])`
Chris Diver
Right -- Jet/ACE can't use a field alias in an ORDER BY, so you have to repeat the exact expression.
David-W-Fenton
+1  A: 

I'm a complete Access noob, but try the second one with a grouping by the non-aggregated columns.

    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
    GROUP BY
       Posts.PostID
       ,Posts.DateCreated
       ,Posts.Title
       ,Posts.Description
       ,Posts.Hits
   ORDER BY
      Count([CommentID]);

Maybe you have to put the JOIN row into braces in MS-Access.

Greets Flo

Florian Reischl
Very interesting thing happened after using GROUP BY: Access limits Description fields size to 254 chars, but field type is actually memo...
HasanGursoy
Ouch! Many database systems have problems with grouping BLOB data (like SQL Server before 2005). However, 254 is not too much...
Florian Reischl
Grouping on memo data is something of a contradiction in terms in the first place. Previous to Jet 4, you couldn't group on them, with Jet 4, it truncates them. This can be avoided (if you have backed yourself into a corner and are forced to group on memo fields) by wrapping the memo in Left() with a suitable length returned to insure no truncation.
David-W-Fenton
A: 
SELECT 
    Posts.PostID, 
    Posts.DateCreated, 
    Posts.Title, 
    Posts.Description, 
    Posts.Hits, 
    dr.CommentCount 
FROM Posts p
INNER JOIN 
    (SELECT PostID, Count(CommentID) as CommentCount FROM PostComments WHERE  
        PostComments.IsApproved=True GROUP BY PostId) dr ON dr.PostID = p.PostID
ORDER BY dr.CommentCount DESC;
Mitch Wheat