I’m trying to get a list of partners (roleid = 511) who posted comments in the month of April of this year.
I would like to embed the count(*) of comments in each row.
And I would like to sort the results by the partner having the most comments first and decend to the partner with the least comments (but > 0) at the bottom of the list.
Here’s what I’ve come up with (which is not working):
SELECT u.uid, u.name, u.mail (SELECT @comment_count:=count(*) FROM comments c WHERE c.uid = u.uid AND EXTRACT(YEAR_MONTH FROM c.timestamp) = '201204') AS april comments, DATE_FORMAT(FROM_UNIXTIME(u.access),'%b-%d-%Y') AS 'last visit'
FROM users AS u
INNER
JOIN users_roles AS ur
ON ur.uid = u.uid
AND ur.rid = 511
WHERE u.mail NOT LIKE '%example.com%' AND EXTRACT(YEAR_MONTH FROM u.access) > '201204' ORDER BY @comment_count;
mysql> describe users;
+------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+----------------+
| uid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(60) | NO | UNI | | |
| pass | varchar(32) | NO | | | |
| mail | varchar(64) | YES | MUL | | |
| mode | tinyint(1) | NO | | 0 | |
| sort | tinyint(1) | YES | | 0 | |
| threshold | tinyint(1) | YES | | 0 | |
| theme | varchar(255) | NO | | | |
| signature | varchar(255) | NO | | | |
| created | int(11) | NO | MUL | 0 | |
| access | int(11) | NO | MUL | 0 | |
| status | tinyint(4) | NO | MUL | 0 | |
| timezone | varchar(8) | YES | | NULL | |
| language | varchar(12) | NO | | | |
| picture | varchar(255) | NO | | | |
| init | varchar(64) | YES | | | |
| data | longtext | YES | | NULL | |
| login | int(11) | NO | | 0 | |
| timezone_name | varchar(50) | NO | | | |
| signature_format | smallint(6) | NO | | 0 | |
+------------------+------------------+------+-----+---------+----------------+
20 rows in set (0.00 sec)
mysql> describe comments;
+-----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+----------------+
| cid | int(10) | NO | PRI | NULL | auto_increment |
| pid | int(10) | NO | MUL | 0 | |
| nid | int(10) | NO | MUL | 0 | |
| uid | int(10) | NO | MUL | 0 | |
| subject | varchar(64) | NO | | | |
| comment | longtext | NO | | NULL | |
| hostname | varchar(128) | NO | | | |
| timestamp | int(11) | NO | | 0 | |
| status | tinyint(3) unsigned | NO | MUL | 0 | |
| format | smallint(6) | NO | | 0 | |
| thread | varchar(255) | NO | | | |
| name | varchar(60) | YES | | NULL | |
| mail | varchar(64) | YES | | NULL | |
| homepage | varchar(255) | YES | | NULL | |
+-----------+---------------------+------+-----+---------+----------------+
14 rows in set (0.00 sec)
mysql> describe role;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| rid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(64) | NO | UNI | | |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
