Code: Select all
mysql> SELECT sos.offender, sos.so, 100 / totals.total * sos.so AS percentage FROM (SELECT phpbb3_posts.poster_id, phpbb3_users.username AS offender, COUNT(*) AS so FROM phpbb3_posts INNER JOIN phpbb3_users ON phpbb3_posts.poster_id = phpbb3_users.user_id WHERE phpbb3_posts.post_text RLIKE '^so[^a-z]' GROUP BY phpbb3_users.username HAVING so >= 10) AS sos INNER JOIN (SELECT phpbb3_posts.poster_id, COUNT(*) AS total FROM phpbb3_posts GROUP BY phpbb3_posts.poster_id) AS totals WHERE sos.poster_id = totals.poster_id ORDER BY so DESC LIMIT 10;
+----------+----+------------+
| offender | so | percentage |
+----------+----+------------+
| James | 62 | 5.2498 |
| ravanwin | 54 | 1.2127 |
| Shannon | 40 | 1.7637 |
| baloo | 34 | 8.8083 |
| dan | 32 | 2.6622 |
| Gaz | 31 | 2.3718 |
| chris | 30 | 1.4641 |
| beev | 29 | 1.8046 |
| chombee | 27 | 2.4150 |
| nix | 24 | 2.7939 |
+----------+----+------------+
10 rows in set (3.83 sec)
Code: Select all
mysql> SELECT sos.offender, sos.so, 100 / totals.total * sos.so AS percentage FROM (SELECT phpbb3_posts.poster_id, phpbb3_users.username AS offender, COUNT(*) AS so FROM phpbb3_posts INNER JOIN phpbb3_users ON phpbb3_posts.poster_id = phpbb3_users.user_id WHERE phpbb3_posts.post_text RLIKE '^so[^a-z]' GROUP BY phpbb3_users.username HAVING so >= 10) AS sos INNER JOIN (SELECT phpbb3_posts.poster_id, COUNT(*) AS total FROM phpbb3_posts GROUP BY phpbb3_posts.poster_id) AS totals WHERE sos.poster_id = totals.poster_id ORDER BY percentage DESC LIMIT 10;
+----------+----+------------+
| offender | so | percentage |
+----------+----+------------+
| baloo | 34 | 8.8083 |
| James | 62 | 5.2498 |
| simone | 23 | 4.1516 |
| nix | 24 | 2.7939 |
| dan | 32 | 2.6622 |
| chombee | 27 | 2.4150 |
| Gaz | 31 | 2.3718 |
| bill | 13 | 2.1417 |
| beev | 29 | 1.8046 |
| Shannon | 40 | 1.7637 |
+----------+----+------------+
10 rows in set (3.93 sec)
I have so only counted users who have started a post with 'so' at least 10 times.
So what?