My goal is: display how often is specific ID repeated as the topic_poster in one table, phpbb_topics, but only if the proper forum_id condition is also met, then also display the corresponding username from another table, phpbb_users.
I have successfully extracted the count of how often is one specific userID occuring as the topic_poster in table phpbb_topics, like that:
SELECT topic_poster, COUNT(topic_poster)
FROM phpbb_topics WHERE forum_id = 156
GROUP BY topic_poster
Thanks to another question on StackOverflow I now also know how to get data from another table to get the username corresponding to the specific userID, like that:
SELECT t.topic_poster, u.user_id, u.username
FROM phpbb_topics t
LEFT JOIN phpbb_users u ON u.user_id = t.topic_poster
I also managed to finally mix the two to get what I want:
SELECT t.topic_poster, COUNT(t.topic_poster), u.user_id, u.username
FROM phpbb_topics t
LEFT JOIN phpbb_users u ON u.user_id = t.topic_poster WHERE t.forum_id = 156
GROUP BY t.topic_poster
However, I do not know how to properly sort in descending or ascending order based on the counter. phpmyAdmin won’t let me just click on the column’s name to sort by it, and any queries i write with GROUP BY or ORDER BY are reporting errors.
Update:
after putting this in:
SELECT t.topic_poster, COUNT(t.topic_poster), u.user_id, u.username
FROM phpbb_topics t
LEFT JOIN phpbb_users u ON u.user_id = t.topic_poster WHERE t.forum_id = 156
ORDER BY COUNT(topic_poster)
the results display only one row:
topic_poster |COUNT(t.topic_poster) | user_id | username
6 | 254 6 | Opix
Same happens if I use this:
SELECT t.topic_poster, COUNT(t.topic_poster), u.user_id, u.username
FROM phpbb_topics t
LEFT JOIN phpbb_users u ON u.user_id = t.topic_poster WHERE t.forum_id = 156
ORDER BY COUNT(t.topic_poster)
Same happens if I use this:
SELECT t.topic_poster, COUNT(t.topic_poster), u.user_id, u.username
FROM phpbb_topics t
LEFT JOIN phpbb_users u ON u.user_id = t.topic_poster WHERE t.forum_id = 156
ORDER BY topic_poster
If I use this: SELECT t.topic_poster, COUNT(t.topic_poster), u.user_id, u.username FROM phpbb_topics t LEFT JOIN phpbb_users u ON u.user_id = t.topic_poster WHERE t.forum_id = 156 GROUP BY t.topic_poster I get all the results, but I can’t sort by the counter.
>Solution :
mySQL extends the group by so you don’t’ have to have one. However, it assumes all values for each column are the same; so it’s free to pick what to put in.
Based on your response, you think you should be getting multiple rows. So that tells me the non-aggregated fields are different so add a group by…
SELECT t.topic_poster, COUNT(t.topic_poster), u.user_id, u.username
FROM phpbb_topics t
LEFT JOIN phpbb_users u ON u.user_id = t.topic_poster
WHERE t.forum_id = 156
GROUP BY t.topic_poster, u.user_id, u.username
ORDER BY COUNT(topic_poster)
You could have ties, so you may also want to order by poster or user name after the count…