XF 2.1 Simple MySQL query for post counts

Wildcat Media

Well-known member
All I want to do is run a MySQL query to find out the raw number of posts a person has. Not what the board is reporting as the number of posts. The ORDER BY and LIMIT are irrelevant here, but with or without them I am getting a MySQL error even after trying several variations therein. This particular version gives me error 1055. Vague help in my searches didn't exactly tell me what error 1055 means.

SQL:
SELECT 
p.user_id,
p.username,
COUNT(p.user_id) AS NumPosts
FROM xf_post p
GROUP BY p.user_id
ORDER BY NumPosts DESC
LIMIT 20;

I did a similar query this morning to find out how many members were ignoring others, and this worked perfectly (and is somewhat similar):

SQL:
SELECT
xf_user.username AS Username,
COUNT(xf_user_ignored.ignored_user_id) AS Number_Ignored
FROM xf_user_ignored
LEFT JOIN xf_user ON xf_user_ignored.ignored_user_id = xf_user.user_id
GROUP BY ignored_user_id
ORDER BY Number_Ignored DESC;
 
The GROUP BY clause needs to have both p.user_id and p.username spelled out:

SQL:
SELECT
p.user_id,
p.username,
COUNT(p.user_id) AS NumPosts
FROM xf_post p
GROUP BY p.user_id, p.username
ORDER BY NumPosts DESC
LIMIT 50

That worked.
 
Back
Top Bottom