Sort the table by COUNT after displaying values from multiple tables-CodePudding

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.

CodePudding user response：

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 from each column. However, if the values are different, what it picks (1 value) isn't representative of the entire set, so you must use group by when the values are different.

Put a different way: if t.forum_ID = 156 limited to a specific topic_poster, user_Id and username. you'd have no problem. But since t.forum_ID represents many different values in each of those columns, group by is needed or the engine will "somewhat" randomly select a value for each of them. The engine assumes all are the same.

Thus the downfall of the mySQL Group by extension. But, if all the non-aggregrated columns did have the same value... you get a performance gain by allowing the engine to just aggregate and 'pick' a value for each column.

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(t.topic_poster)

You could have ties, so you may also want to order by poster or user name after the count...