group by statement error

[s4dadmin]

I have an sql statement that is giving me this error.

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[MySQL][ODBC 3.51 Driver][mysqld-4.0.12-max-debug]Can't group on 'lngtopicount'

/forum/Default.asp, line 83

Here is the sql statement

SQL = "SELECT Category.CategoryName, Forums.ForumID," _
& " Forums.ForumName, Forums.ForumDescription," _
& " Count(Topics.TopicID) As lngtopicount, Count(Posts.PostID) As lngpostcount," _
& " Posts.PostDate As lastpostdate,Members.Username As lastpostmember" _
& " FROM Category,Forums,Topics,Posts,Members" _
& " Where Category.CategoryID = Forums.CategoryID" _
& " AND Forums.ForumID = Topics.ForumID" _
& " AND Topics.TopicID = Posts.TopicID" _
& " AND Posts.MemberID = Members.MemberID" _
& " GROUP BY Category.CategoryName,Forums.ForumID,Forums.ForumName,Forums.ForumDescription,lngtopicount;"

The error is with using the aliases: lngtopicount, lngpostcount,lastpostdate,lastpostmember

If I don't use these aliases in the sql statement there will be no errors and the page will show but the incorrect information will be showing. How do you include the aliases in the group by statement?

 

[swampBoogie]

Why do you include the counts in the group by clause?

 

[s4dadmin]

This sql statement is for a forum just like this one which shows the number of topics and posts. I made the conversion from access to mysql which does not support sub select statements. I want to include the counts because it is showing the same number of topics and replies for each forum which is not true. It is only pulling the top row.