Hello,

As I am preparing my site to move to AF 4.1 soon I also want to get my mini Top 10 users script working again. I am no SQL specialist so here is what I did:

SELECT TOP 10 AuthorName AS Name , COUNT(*) AS Posts FROM dbo.activeforums_Content INNER JOIN Users ON UserID=AuthorId GROUP BY AuthorName ORDER BY COUNT(*) DESC

This works well but I would to show 'Displayname' instead of 'Authorname'. I think I need to get that from the Users table but I have no ideas on how to do that

<img src='http://www.activemodules.com/DesktopModules/ActiveForums/themes/activemodules/emoticons/wow.gif' align="absmiddle" border="0" />

Bump...

you have already joined to the user table, just change authorname to displayname in the 2 places it appears (select and group by) and you should be good.

SELECT TOP 10
displayname AS Name ,
COUNT(*) AS Posts
FROM dbo.activeforums_Content
INNER JOIN Users ON UserID=AuthorId
GROUP BY displayname
ORDER BY COUNT(*) DESC

Query is above, you might consider only taking from the current week/month/3 months something rather than pulling from all time

Thanks. Now that is strange, the script gives me 12904 posts for myself but in AF is 12912...how can that happen?

Sure np, the query has no criteria so it will pull anything, regardless of moderation and draft status. I am not sure what criteria the AF query is using but the difference is something with draft, moderation or similar.

Don't join on the content table. You just want the user, correct? Join on the activeforums_UserProfile table and Users table. All the numbers you want for the post counts are in the activeforums_UserProfile table.

Even better yet

This is my last version:

SELECT TOP 10
dbo.Users.DisplayName AS Name ,
SUM(dbo.activeforums_UserProfiles.ReplyCount
+ dbo.activeforums_UserProfiles.TopicCount) AS Posts
FROM dbo.activeforums_UserProfiles
INNER JOIN USERS ON dbo.activeforums_UserProfiles.UserId = dbo.Users.UserID
AND dbo.activeforums_UserProfiles.PortalId = '0'
GROUP BY DisplayName
ORDER BY Posts DESC

Mariette, do you have an example of this somewhere?

I got this working, but I also need to reference the USERID, can someone help modify this query.

Right now its displaying DISPLAY NAME and # of POSTS. I just need it to also include the USER for the user.