0

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.

kerstoff0mega
  • 57
  • 5
  • Does this answer your question? [MySQL "Group By" and "Order By"](https://stackoverflow.com/questions/1066453/mysql-group-by-and-order-by) – devlin carnate Mar 15 '22 at 17:36
  • I've read that before, it doesn't tell me how to sort using the counter :( – kerstoff0mega Mar 15 '22 at 17:38
  • use the principles in those answers and `ORDER BY COUNT(topic_poster)`. If you're still not getting it, update your question with your best attempt at resolving this yourself and explain what results you're getting versus the result you expect – devlin carnate Mar 15 '22 at 17:42
  • do you mean you need to `group by t.topic_poster, u.user_ID, u.username` then have your order by? it makes sense you'd get 1 row since you don't have a group by and the system is free to pick the value of each of the other non-aggregated columns... assuming the mysql has it's [group by extension](https://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html) enabled the fact you don't get an error on the other non-aggregrated columns tells me it is. – xQbert Mar 15 '22 at 17:54
  • @devlincarnate Updated the attempts. – kerstoff0mega Mar 15 '22 at 17:54

1 Answers1

2

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...

xQbert
  • 34,733
  • 2
  • 41
  • 62
  • 1
    Thank you, it works! I'll make sure to get it to my backup folder and remember to work off of this in future. :) – kerstoff0mega Mar 15 '22 at 18:00
  • @kerstoffOmega I'd encourage you to disable the group by extensions so you don't run into this in the future [outlilned](https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sqlmode_only_full_group_by) may save future headache and ensures a more standardized SQL statement. but, if others are using this database and have been for awhile, you may not want to as it could break existing code. – xQbert Mar 15 '22 at 18:02
  • Yes, unfortunately, the base is 16 years old, no joke. Too much risk, legacy built on legacy... will need to rebuild it from scratch on my own one day :P – kerstoff0mega Mar 15 '22 at 18:07
  • It "Shouldn't" break existing code if people were following proper use of the extended group by; but most don't. So given the age, I'd stick with what you have. Current versions of mySQL has this turned off by default I believe. Older versions had it enabled and drove non-mysql users crazy because you can't have aggregation w/o a group by except in mySQL as far as I know. – xQbert Mar 15 '22 at 18:08